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.

CREATE TABLE Employee (
   EmployeeID  [int] ,
   FirstName   [varchar](50) ,
   LastName    [varchar](50) ,
   Department  [varchar](20) ,
)

This means that you can insert null into them.

insert into Employee (EmployeeID, FirstName, LastName, Department) 
values (null, null,null,null)

Select * from Employee


EmployeeID    FirstName    LastName    Department
----------    ---------    --------    ----------
NULL	      NULL         NULL        NULL

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.

CREATE TABLE Employee (
	EmployeeID  [int]         NOT NULL ,
	FirstName   [varchar](50) NOT NULL ,
	LastName    [varchar](50) NOT NULL ,
	Department  [varchar](20) NULL    ,
)


insert into Employee (EmployeeID, FirstName, LastName, Department) 
values (1,'Olive','Yew')

insert into Employee (EmployeeID, FirstName, LastName, Department) 
values (2,'Aida','Bugg',null)


Select * from Employee



EmployeeID    FirstName    LastName    Department
----------    ---------    --------    ----------
1             Olive        Yew         NULL
2             Aida         Bugg        NULL

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

insert into Employee (EmployeeID, FirstName, LastName, Department) 
values (3,null,'Austin','Accounts')


Cannot insert the value NULL into column 'FirstName', table 'DB.dbo.Employee'; 
column does not allow nulls. INSERT fails.
The statement has been terminated.

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.

CREATE TABLE Employee (
    EmployeeID  [int] CONSTRAINT NN_Employee_EmployeeID NOT NULL,
)

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

Alter table Employee DROP CONSTRAINT NN_Employee_EmployeeID


*** Result ***
'NN_Employee_EmployeeID' is not a constraint.
Could not drop constraint. See previous errors.

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.

CREATE TABLE Employee (
	EmployeeID  [int]         NOT NULL ,
	FirstName   [varchar](50) NOT NULL ,
	LastName    [varchar](50) NOT NULL ,
	Department  [varchar](20) NULL    ,
)

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

insert into Employee (EmployeeID, FirstName, LastName, Department) 
values (1,'Olive','Yew','Admin')

insert into Employee (EmployeeID, FirstName, LastName, Department) 
values (2,'Aida','Bugg',null)  //Department is NULL

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

Alter Table Employee Alter Column Department  [varchar](20) NOT NULL


*** ERROR ***
Cannot insert the value NULL into column 'Department', table 'DB.dbo.Employee'; column does not allow nulls. UPDATE fails.

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.

Update Employee Set Department='' Where Department is NULL

Now, you can change the Department column to NOT NULL

Alter Table Employee Alter Column Department  [varchar](20) NOT NULL

Add NOT NULL Column to existing table

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

//Default is NULL
Alter Table Employee Alter Column Department  [varchar](20)

or 
Alter Table Employee Alter Column Department  [varchar](20) NULL