Menu
I want to add a nullable boolean column to an existing table with default value of null.
I have used this bit of script but it does not set the default value to null. it sets it to 0 instead.
ADD newColumnName BIT NULL
CONSTRAINT DF_tableName_newColumnName DEFAULT(null)
926
For each row in table, a column can contain a value or NULL which indicates "no value." If a column is declared NOT NULL, it must always contain a non-NULL value; NULL is not allowed. Columns can have a default value.
By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
I just ran your example code snippet on my SQL Server 2008 R2 instance and then inserted a record. It initialized the column to null, as expected. The next step would be to post the alter statement and the insert statement that you used.
I used:
alter table tmp1 Add newColumnName bit null CONSTRAINT DF_tableName_newColumnName DEFAULT(null)
insert into tmp1(emp_id) values(9999)
select * from tmp1
After running the above, I used SQL Server Management Studio "Design" action to examine the properties of the new column. It showed that the "Default Value or Binding" was indeed (Null) as expected.
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
