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.
Table of Contents
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.
1 2 3 4 5 6 7 8 | CREATE TABLE Employee ( EmployeeID [int] , FirstName [varchar](50) , LastName [varchar](50) , Department [varchar](20) , ) |
This means that you can insert null into them.
1 2 3 4 5 6 7 8 9 10 11 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | 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.
1 2 3 4 5 6 7 8 9 | 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.
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 6 7 | 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.
1 2 3 | Update Employee Set Department='' Where Department is NULL |
Now, you can change the Department column to NOT NULL
1 2 3 | 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.
1 2 3 4 5 6 7 | //Default is NULL Alter Table Employee Alter Column Department [varchar](20) or Alter Table Employee Alter Column Department [varchar](20) NULL |
