How to add NOT NULL constraint along with default value for the column?

Question

I have a column 'name' in student table. I need to add NOT NULL constraint on this column. But I get SQL error saying cannot add null constraint since the existing rows in the table has null values in the column. How would I add a null constraint along with default value in a single alter statement. Below is my query.

alter table Student alter column name nvarchar NOT NULL;

• Dale K: You have to update the existing rows first. But you can add your constraint with NO CHECK which doesn't validate existing data.

Answer 1

But I get SQL error saying cannot add null constraint since the existing rows in the table has null values in the column.

Have you tried overwriting the existing NULL values?

You can't have a constraint on a column when existing values would violate that constraint. You need to make your table compliant first.

• swetha: So is not there a way that i can add default values to existing columns and add NOT NULL in a single ALTER query
• Tomalak: @swetha No. The purpose of a constraint is to guarantee that there can be no values in a column that violate a given rule. If there was a way to add a constraint while violating values are present, constraints would be useless.
• Jan Madeyski: I usually do something like that (1) UPDATE tbl SET col = 0 WHERE col IS NULL, (2) ALTER TABLE tbl ALTER COLUMN col int NOT NULL, (3) ALTER TABLE tbl ADD CONSTRAINT df_tbl_col DEFAULT(0) FOR col
• Damien_The_Unbeliever: @swetha - no, because the default is only considered during an insert operation. It's not retrospectively applied to columns that were inserted with nulls (indeed, it's valid to have a column with a default that also allows nulls and has some)

Answer 2

SQL Server does not make this easy. I think the only way is to set the existing values to the default that you want. Then change the column to have a default value and not null:

-- Get rid of the existing `NULL` values
update students set name = '' where name is null;
-- Add the NOT NULL constraint
alter table students
 alter column name varchar(255) not null;
-- Add a default value
alter table students
 add constraint df_t_name default '' for name ;

Here is what it looks like in practice.