user6592730 user6592730 - 1 month ago 6
SQL Question

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

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.

Answer

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

Comments