How to add a default constraint to a nullable column in SQL Server?

Question

I wanted to add default values to a nullable field in SQL Server. I used the following t-sql

ALTER TABLE dbo.tblMain ADD  CONSTRAINT [default_0]  DEFAULT ((0)) FOR [Sum]

This constraint will change any new values added to the Sum field in the tblMain table to 0 but it does not change the null values that was already in the column to 0.

I also tried the WITH VALUES option

ALTER TABLE dbo.tblMain ADD  CONSTRAINT [default_0]  DEFAULT ((0)) FOR [Sum]  WITH VALUES

This did not change anything.

I know I can add the constraint and do something like:

UPDATE dbo.tblMain set Sum = 1 where Sum is null

But I want to know if I can do it using constraint in one scoop.

A constraint is used to prevent bad data from getting in, it does not update existing data. Also, keep in mind that a default constraint on a nullable column does NOT prevent NULL from being added. You can still get NULL if you don't include that column in your insert. — Sean Lange
– Sean Lange, Commented Feb 25, 2019 at 21:13
As Sean said - Default constraints will not change existing data. What I would do in this situation (if constantly updating NULLs is not desirable) is to create a view with an ISNULL(<yourfield>,0) — Alan Burstein
– Alan Burstein, Commented Feb 25, 2019 at 21:36
or alter the column - first update to replace nulls with values, then alter the column to not null. this should be done inside a transaction with isolation level serializable. — Zohar Peled
– Zohar Peled, Commented Feb 25, 2019 at 21:52

Han Zhao · Accepted Answer · 2024-12-06 19:35:06Z

Conclusion

To enforce existing rows in nullable columns to be updated with default values (thanks @Sean Lange for pointing it out),

you are not able to achieve it by using ALTER TABLE ADD CONSTRAINT against existing column (e.g., column created first and then adding constraint)
you are able to do it by adding a new column and applying constraint at the same time (i.e., ALTER TABLE ADD column_name CONSTRAINT constraint_name)

Explanation

Below scripts are tested against Microsoft SQL Server 2016.

Adding constraint when creating new nullable column at the same time

CREATE TABLE #temp_db (col_a INT, col_b INT)

INSERT INTO #temp_db (col_a, col_b) VALUES (1, 2), (3, 4)
SELECT * FROM #temp_db

ALTER TABLE #temp_db
ADD col_c INT
CONSTRAINT col_c_def DEFAULT 0 WITH VALUES

SELECT * FROM #temp_db

INSERT INTO #temp_db (col_a, col_b) VALUES (5, 6), (7, 8)
SELECT * FROM #temp_db

DROP TABLE #temp_db

Note, make sure you apply WITH VALUES, otherwise existing rows will be set as NULL (reference).

ALTER TABLE #temp_db
ADD col_c INT
CONSTRAINT col_c_def DEFAULT 0 --WITH VALUES

Adding constraint after creating new nullable column

CREATE TABLE #temp_db (col_a INT, col_b INT)

INSERT INTO #temp_db (col_a, col_b) VALUES (1, 2), (3, 4)
SELECT * FROM #temp_db

ALTER TABLE #temp_db
ADD col_c INT

ALTER TABLE #temp_db
ADD CONSTRAINT col_c_def DEFAULT 0 FOR col_c WITH VALUES

SELECT * FROM #temp_db

INSERT INTO #temp_db (col_a, col_b) VALUES (5, 6), (7, 8)
SELECT * FROM #temp_db

DROP TABLE #temp_db

Note, applying WITH VALUES or not makes no difference to the result.

For non-nullable columns, you have to create it and specify default value at the same time, as SQL server has no idea what values should be put into existing rows.

ALTER TABLE #temp_db
ADD col_c INT NOT NULL
CONSTRAINT col_c_def DEFAULT 0

Collectives™ on Stack Overflow

How to add a default constraint to a nullable column in SQL Server?

1 Answer 1

Conclusion

Explanation

Adding constraint when creating new nullable column at the same time

Adding constraint after creating new nullable column

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Conclusion

Explanation

Adding constraint when creating new nullable column at the same time

Adding constraint after creating new nullable column

Comments

Your Answer

Sign up or log in

Post as a guest

Related