[sql] 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.

This question is related to sql sql-server alter-table

The answer is


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


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;

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;

Below query worked for me with default value false;

ALTER TABLE cti_contract_account ADD ready_to_audit BIT DEFAULT 0 NOT NULL;


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to alter-table

How to add a boolean datatype column to an existing table in sql? how to modify the size of a column MySQL: ALTER TABLE if column not exists Hive Alter table change Column Name Alter table to modify default value of column Rename column SQL Server 2008 How to delete a column from a table in MySQL Altering column size in SQL Server ALTER TABLE on dependent column ALTER TABLE to add a composite primary key