Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I add a column to a table in SQL Server that doesn't allow nulls?

Tags:

sql

sql-server

I have a table that I want to add a bit column, which I wish to default to false for all existing data.

How do I alter my table in such a way that it allows me to specify NOT NULL before I have inserted false for my existing rows?

Should I create it as nullable, do an insert than switch it non-nullable?

like image 418
KingNestor Avatar asked Dec 22 '09 16:12

KingNestor


People also ask

How do I add a column to an existing table with not null in SQL?

You can add a not null column at the time of table creation or you can use it for an existing table. In the above table, we have declared Id as int type that does not take NULL value. If you insert NULL value, you will get an error. Here is the query to add a not null column in an existing table using alter command.

How do I change NOT NULL column to allow nulls in SQL Server?

To do this, you need to use the ALTER TABLE ALTER COLUMN statement as follows: ALTER TABLE table_name ALTER COLUMN column_name DATA_TYPE [(COLUMN_SIZE)] NULL; In this syntax: First, specify the name of the table from which you want to change the column.

How do you add NOT NULL constraints in existing columns?

To add not null constraint to an existing column in MySQL, we will use the ALTER command. This is a type of validation to restrict the user from entering null values.


1 Answers

You could add the column and provide the default value to be used for all existing rows.

ALTER TABLE foo  ADD bar bit  DEFAULT 0 NOT NULL; 
like image 95
Robert Christie Avatar answered Oct 02 '22 13:10

Robert Christie