SQL NOT NULL Constraint

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.

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

SQL Primary Key

SQL Composite Key

SQL Foreign key

SQL Default Constraint

SQL Unique Key

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 2,540 total views,  2 views today