SQL Server How to add a boolean datatype column to an existing table in sql?

nhhxz33t  于 2023-05-05  发布在  其他
关注(0)|答案(4)|浏览(137)

I have a table called person in my database. I want to add another column to the same table and it's a Boolean datatype column. I have tried following queries but it says syntax error near default. I know this is a common and there are lot of answers. I have tried many of them and couldn't figure out to make it work. So please help me.

queries I have tried

ALTER TABLE person add column "AdminApproved" BOOLEAN SET default FALSE;
ALTER TABLE person alter column "AdminApproved" BOOLEAN SET default FALSE;

I have tried without SET key word too.

pokxtpni

pokxtpni1#

In SQL SERVER it is BIT , though it allows NULL to be stored

ALTER TABLE person add  [AdminApproved] BIT default 'FALSE';

Also there are other mistakes in your query

  1. When you alter a table to add column no need to mention column keyword in alter statement
  2. For adding default constraint no need to use SET keyword
  3. Default value for a BIT column can be ('TRUE' or '1') / ('FALSE' or 0) . TRUE or FALSE needs to mentioned as string not as Identifier
8i9zcol2

8i9zcol22#

The answer given by Pரதீப் creates a nullable bool, not a bool, which may be fine for you. For example in C# it would create: bool? AdminApproved not bool AdminApproved .

If you need to create a bool (defaulting to false):

ALTER TABLE person
    ADD AdminApproved BIT
    DEFAULT 0 NOT NULL;
sy5wg1nm

sy5wg1nm3#

In phpmyadmin, If you need to add a boolean datatype column to an existing table with default value true:

ALTER TABLE books
   isAvailable boolean default true;
vx6bjr1n

vx6bjr1n4#

There are two ways that you can do it.

ALTER TABLE `tablename`
ADD `column_name` BOOLEAN DEFAULT FALSE 

ALTER TABLE `tablename`
ADD `column_name` TINYINT DEFAULT 0

相关问题