SQL NOT NULL Constraint is used to prevent inserting NULL values into the specified column.
When value for any specific column is not provided while inserting, or updating a record into a table, by default it takes NULL value.
So, by specifying NULL constraint, you can be sure that a particular column(s) cannot have NULL values.
Lets look at an example on SQL NOT NULL Constraint.
Specifying NOT NULL Constraint while creating a new table in SQL Server
In following statement, we are Creating a new table named as TranData. As you can see, table contains two columns TranId and TranCode.
We have specified NOT NULL constraint on TranId column which means value for this column can not be null and It is must to provide value for this column while inserting and updating records in table.
While TranCode column can accepts null values, It is not mandatory to provide value for this column while inserting and updating records in table.
If you do not specify NOT NULL constraint on column, By default SQL Server allow the column to accept null value.
CREATE TABLE dbo.TranData ( TranId INT NOT NULL, TranCode VARCHAR(100) NULL )
Inserting values into NOT NULL Constraint column
Lets Insert a record in table TranData inorder to check the ability of NOT NULL constraint which is specified on TranID column.
First we will provide a value for TranId column.
INSERT INTO dbo.TranData (TranID, TranCode) VALUES (1, 'XCV-SU-13')
You can see, commands completed succesfully. As value for TranID is provided.
Lets check the table, to make sure record is inserted or not, and you can see record is inserted succesfully in table.
SELECT * FROM dbo.TranData
Lets, not provide the value for TranID column and Insert value only for TranCode in table.
INSERT INTO dbo.TranData
(TranCode)
VALUES
('ZCV-FHF-15')
Lets check what happens when we do not provide the value for TranCode column, as we have not specified NOT NULL constraint on this column.
INSERT INTO dbo.TranData (TranId) VALUES (2)
Lets check the table, you can see records is inserted and NULL is assigned to TranCode by SQL Server.
SELECT * FROM dbo.TranData
Specifying a NOT NULL Constraint on existing table column
You can also add NOT NULL constraint on existing table, Lets say you want to add NOT NULL constraint on column TranCode of table TranData.
Before Specifying NOT NULL constraint to any column of existing table you must make sure that column should not have any null values. because when you create a NOT NULL constraint on any existing table column, SQL Server first check the existing values for that column to ensures that value should not be null.
In our case you can see below screenshot, TranCode column contains null value, So first we should replace this null value with blank value.
Lets see what happens when you try to create a NOT NULL constraint on column that already contains null values.
Following Alter table Alter column statement adds the NOT NULL constraint on column TranCode.
ALTER TABLE dbo.TranData ALTER COLUMN TranCode VARCHAR(100) NOT NULL
So to avoid such error, better to cross check first, if column does not have any null value then create NOT NULL Constraint. But if column have already some existing null values than you should first Update those values with blank Values.
Lets update null values for TranCode column with Blank using Update statement as shown below.
UPDATE dbo.TranData SET TranCode = '' WHERE TranCode IS NULL
Lets check whether the null value for record is update with blank or not.
SELECT * FROM dbo.TranData
Now you can specify NOT NULL constraint on TranCode column, execute above script again.
And you can see this time you will not get any error.
Lets try to insert NULL value for TranCode column to ensures whether the NOT NULL constraint allows null values or not for this column.
INSERT INTO dbo.TranData (TranId) VALUES (3)
You can see, it gives an error. That means NOT NULL constraint ensures that value for TranCode column must be provided.
Removing NOT NULL Constraint from column
You can also remove the NOT NULL constraint from existing column.
Lets remove the NOT NULL Constraint from column TranCode using Alter table Alter column statement
ALTER TABLE dbo.TranData ALTER COLUMN TranCode VARCHAR(100) NULL
As you can see, command completed successfully, that means NOT NULL constraint is removed from column TranCode.
Lets try to insert null values for TranCode column as shown below.
INSERT INTO dbo.TranData (TranId) VALUES (4)
You can check table, and you will see now null values is allowed for TranCode column which ensures that
NOT NULL constraints has been removed from column.
SELECT * FROM dbo.TranData
SQL Server Constraints
