NOT NULL Constraint in SQL Server

NOT NULL Constraint in SQL server specifies that the column cannot store a NULL value. All inserts & updates to the column must specify a value. Attempting to insert or update NULL value will result in the error.

Nullable Columns

The SQL Server by default allows storing the NULL value in a column. We call such columns Nullable columns

The Following example creates Employee Table. SQL Server creates all the above columns as nullable columns.

This means that you can insert null into them.

NOT NULL Constraint

To make a column Not Nullable use the NOT NULL Constraint on the column.

The following query creates the Employee Table with EmployeeID & FirstName & LastName as Non-Nullable Columns.

When trying to insert a NULL into a not nullable column like FirstName, SQL Server throws “column does not allow nulls” error.

Naming the NOT NULL Constraint

SQL Server stores the NOT NULL as a boolean attribute of the column. It does not store it anywhere else. Hence you cannot give a name to the NOT NULL Constraint.

But, Syntax allows you to name the constraint. The following query creates a NOT NULL constraint with the name NN_Employee_EmployeeID. But SQL Server discards it.

Hence deleting the NOT NULL constraint using the name will result in an error.

Modify Exitsting Column to NOT NULL

You can use Alter Table query to change the nullability of an existing column.

But the SQL Server will not convert a NULL column to NOT NULL if the column currently contains null values.

The Employee table below contains the Department column which is a Nullable column.

The following queries insert the null value to the department column.

When we try to change the Department to NOT NULL, the SQL server throws the “Cannot insert the value NULL into column” error.

To solve this issue, first, we need to update the Department column and remove all NULL values. The following query updates the Department column to an empty space where its value is NULL.

Now, you can change the Department column to NOT NULL

Add NOT NULL Column to existing table

To change a column from NOT NULL to NULL run the following query.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top