Sunday, August 16, 2020

Not NULL constraint in SQL Server

The Not NULL constraint enforces a column not to accept NULL values.
By default columns are able to hold NULL values. So for a Not NULL constraint need to provide valid not null value for any insert and update operation.

Consider following examples.

   Name VARCHAR(50) NULL

insert into consDemo(Name) values

This will throw error:

Cannot insert the value NULL into column 'ID', table 'dbo.consDemo'; column does not allow nulls. INSERT fails.

insert into consDemo(ID,Name) values
(1,'Harish Rao')

insert into consDemo(ID) values

You will see that first record will fail since we are trying to insert value only on Name column without providing value on ID column.
Other two records will be inserted since Name column is not mandatory and it will accept NULL values.

To enforce Not NULL constraint on a column make sure column does not contain any NULL values.

UPDATE consDemo SET [Name]='' WHERE [Name] IS NULL

Then modify the column name.
Alter table consDemo alter column name varchar(50) Not NULL


  1. Excellent information, Thanks for publishing such essential information. You are doing such a good job. This information is very helpful for everyone. Keep it up. Thanks. small business phone service