How to Change a Nullable Column to NOT NULL with Default Value in SQL Server

We have a table that has a nullable column (column C1) with some inserted NULL values:

nullable_column_null_values

We want to change the nullable C1 column to NOT NULL with default value (it will be value 'w').

We will add the default constraint (value 'w') to the column C1:

ALTER TABLE dbo.Test1
ADD CONSTRAINT col_c1_def DEFAULT 'w' FOR C1

Then we need to update all the records that are NULL to the value that will be the default value, before changing the column to NOT NULL:

UPDATE dbo.Test1
SET C1 = 'w'
WHERE C1 IS NULL

Now the table column C1 has no NULL values:

nullable_column_null_values_replaced

Now we can change the column to NOT NULL:

ALTER TABLE dbo.Test1
ALTER COLUMN C1 nvarchar(50) NOT NULL

Read related article:
How to Change a Nullable Column to NOT NULL

If the post helped you, please share it:
Pin It

1 comment to How to Change a Nullable Column to NOT NULL with Default Value in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">