Add nullable column to SQL Server with default value of null

Question

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)

Why do you need a default constraint on a nullable column, especially when you need the value to be NULL — Raj
– Raj, Commented Feb 3, 2016 at 4:36
I have 3 states for the value of new column. true, false, null. when the user fills out a form in the application. they can opt to answer to a question with yes or no or they can leave the question unanswered. reaction to No and not answered is not going to be the same. And I need this property of users to be defaulted as null, as if they have not answered to that question. — Maryam
– Maryam, Commented Feb 3, 2016 at 4:44
The default is always NULL - you don't need to explicitly add a default. — Nick.Mc
– Nick.Mc, Commented Feb 3, 2016 at 5:11
I believe in SQL the value of a field is NULL by default, no need to explicitly set it to NULL using a DEFAULT constraint. — CodeConnoisseur
– CodeConnoisseur, Commented Nov 21, 2019 at 15:32

Ted Cohen · Accepted Answer · 2016-02-03 04:52:13Z

17

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.

edited Feb 3, 2016 at 4:52

answered Feb 3, 2016 at 4:46

Ted Cohen

1,05110 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Add nullable column to SQL Server with default value of null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related