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.

CREATE TABLE consDemo

(

   ID INT NOT NULL,

   Name VARCHAR(50) NULL

)

insert into consDemo(Name) values

('Rohit')

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

(2)

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

Different types of SQL Server Constraints:

• Not Null Constraint
• Check Constraint
• Default Constraint
• Unique Constraint
• Primary Key Constraint
• Foreign Key Constraint