NOT NULL constraints are important constraints in SQL Server to ensure the column defined on never accepts NULL values. By default, column accepts NULL value in SQL Server. If we need to ensure the column should not accept NULL values, then we can define a NOT NULL constraints on the column. We can create NOT NULL constraint in 3 ways:

• while creating a table
• using alter table statement
• using SQL Server management Studio

Let us see an example:

Create Table TestTable ( ID int NOT NULL , FirstName varchar(50) )

Insert into TestTable Values(1,'SQL'),(NULL, 'Zealot')

Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column ‘ID’, table ‘SQLZealot.dbo.TestTable’; column does not allow nulls. INSERT fails.
The statement has been terminated.

Now, let us insert some data with NULL values for FirstName column and define a NOT NULL constraint on First Name column as below.

Insert into TestTable Values(2,NULL)

ALTER TABLE TestTable ALTER COLUMN FirstName VARCHAR(50) NOT NULL

Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column ‘FirstName’, table ‘SQLZealot.dbo.TestTable’; column does not allow nulls. UPDATE fails.
The statement has been terminated.

This error has raised because we have already inserted a record that has NULL value for First Name column in the table. That means, when we create a constraint, it checks the existing data before it creates the constraints.

If you enjoyed this blog post, feel free to share it with your friends!