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

Question

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.

Nothing on Google if you search 'Boolean data type sql server'?? You are correct, this is extremely common and there are A LOT OF ANSWERS OUT THERE. — dfundako
– dfundako, Commented Oct 25, 2016 at 15:40

Pரதீப் · Accepted Answer · 2016-10-25 15:43:53Z

65

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

When you alter a table to add column no need to mention column keyword in alter statement
For adding default constraint no need to use SET keyword
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

edited Oct 25, 2016 at 15:43

answered Oct 25, 2016 at 15:38

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sean Lange Over a year ago

Just a caveat here. BIT is NOT a true boolean. It allows 3 states, 0, 1 and NULL. It is subtle but very important to understand. Also you can't code against it like a boolean.

Nabeel Over a year ago

This creates a nullable bool. This is correct : ALTER TABLE [person] add [AdminApproved] BIT DEFAULT 0 NOT NULL;

MEC · Accepted Answer · 2017-09-06 09:07:23Z

46

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? AdminApprovednot bool AdminApproved.

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

    ALTER TABLE person
    ADD AdminApproved BIT
    DEFAULT 0 NOT NULL;

answered Sep 6, 2017 at 9:07

MEC

1,7561 gold badge17 silver badges23 bronze badges

Comments

Zafer Faheem · Accepted Answer · 2020-03-02 13:19:26Z

1

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;

answered Mar 2, 2020 at 13:19

Zafer Faheem

2,0141 gold badge9 silver badges7 bronze badges

Comments

Danushka-Madushan · Accepted Answer · 2023-05-01 19:32:48Z

0

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

answered May 1, 2023 at 19:32

Danushka-Madushan

11 bronze badge

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related