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

user_13876953 0
Viewed 3656

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;
sql-server sql
2 Answers

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.

user_11440350

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.

Tomalak_187710
Related