SQL Server NOT NULL Constraint

The SQL Server NOT NULL constraint is used to ensure that when we insert the rows to the table the column is never NULL. It throws an error when there is no value for a particular column when it is with a NOT NULL constraint. The primary key by default comes with the NOT NULL and UNIQUE constraints so the primary key can’t be NULL at any time. The NOT NULL constraint for the column can be added by declaring at the time of creation of the table and also during the declaration while adding an extra column to the table using the ALTER command. In this article, you will get a clear understanding of NOT NULL Constraints.

General Syntax

CREATE TABLE table_name
(
column1  data_type,
column2 data_type NOT NULL 
)

Explanation: In the above query, we have applied the NOT NULL Constraints on column2.

Usage of the NOT NULL Constraint

Step 1: Create database Geeksforgeeks by using the following SQL query:

CREATE DATABASE Geeksforgeeks

Step 2: Use the GFG Database.

USE Geeksforgeeks

Step 3: Create a table with the name studentsMark which contains the studentNames with NOT NULL constraint along with their details

CREATE TABLE studentsMarks(
studentId INT PRIMARY KEY,
studentName VARCHAR(100) NOT NULL,
courseId VARCHAR(100),
marksObtained INT,
);

Step 4: View the description of the tables

EXEC sp_columns   studentsMarks

The Result Looks Like :

StudentMark Schema

Explanation : row 1 and row 2 have IS_NULLABLE as NO since studentId is a primary key and studentName has NOT NULL constraint.

Step 5: Inserting rows into the table.

INSERT INTO studentsMarks
VALUES (19104060, 'Student1', 'ECPC-1001', 25),
(19104061, 'Student2', 'ECPC-1001', 96),
(19104062, 'Student3', 'ECPC-1001', 81),
(19104063, 'Student4', 'ECPC-1001', 85),
(19104064, 'Student5', 'ECPC-1001', 86),
(19104065, 'Student6', 'ECPC-1001', 55),
(19104066, 'Student7', 'ECPC-1001', 70),
(19104067, 'Student8', 'ECPC-1001', 71),
(19104068, 'Student9', 'ECPC-1001', 65),
(19104069, 'Student10', 'ECPC-1001', 68),
(19104071, 'Student11', 'ECPC-1001', 25);

Step 6: Check the table

SELECT * FROM studentsMarks

The Result Looks Like:

StudentMarks Table

Let’s see whether inserting NULL values for a column with NOT NULL constraint throws an error or NOT and what will be the error.

Query:

INSERT INTO studentsMarks
VALUES (19104072, NULL, 'ECPC-1001', 75)

The Result Looks Like:

Error Messages

Explanation : In the query we have inserted a record with a studentId = ‘19104072‘ which has a studentId = NULL, we have got an error since we have added a NOT NULL constraint.

How to Remove the NOT NULL Constraint to Allow NULL Values

There may be scenarios where after a particular time the column may accept the NULL since there might be reduced importance of that column. In such cases, the NOT NULL constraint should be removed.

Note: The NOT NULL constraint cannot be dropped for the primary key column.

Let’s see how to remove the NOT NULL other than the Primary Key.

ALTER TABLE studentsMarks
ALTER COLUMN studentName VARCHAR(100) NULL

Let’s check the column specification of ‘studentName‘ whether it is changed to NULLABLE or NOT.

EXEC sp_columns   studentsMarks

The Result Looks Like:

Schema after Removing NOT NULL

Explanation: In the 2nd row which has the properties of ‘studentName‘ column we can see IS_NULLABLE = YES. It means it can store the null values in it.

Let’s see if inserting a row with studentName as NULL works now or not.

INSERT INTO studentsMarks
VALUES (19104072, NULL, 'ECPC-1001', 75)

SELECT * FROM  studentsMarks

The Result Looks Like:

Table After check Null value can be inserted or not

Explanation: In the query we have inserted a row with a studentId = ‘19104072‘ which has studentName as NULL, As you can clearly see the column studentName store NULL in it. After the NULL Constraint has been removed.

Adding a New Column with a NOT NULL Constraint

In scenarios, where we may want to add a new column with a NOT NULL constraint, we can add a new column with the ALTER command during the declaration of the column with the NOT NULL constraint. To add a column with a NOT NULL constraint, firstly the column must be added with some default value and then add the NOT NULL constraint so that existing rows will have the default value for that column and the new rows that will be going to insert will have the NOT NULL constraint. The default constraint can be later removed or can be kept like that only.

Let’s see How to Add a Column with a NOT NULL Constraint.

Query

ALTER TABLE studentsMarks
ADD gradeObtained VARCHAR(100) DEFAULT  'NA' 

ALTER TABLE studentsMarks
ALTER COLUMN gradeObtained VARCHAR(100) NOT NULL

SELECT * FROM studentsMarks

Explanation: In the queries here in the first ALTER statement we have an add the column called gradeObtained with the default value of ‘NA‘ and then in the second ALTER statement we have removed the DEFAULT constraint and added the NOT NULL constraint with it.

The Result Looks Like:

Let’s check the column specification whether ‘gradeObtained‘ is changed to NOT NULLABLE or NOT

EXEC sp_columns   studentsMarks

The Result Looks Like:

Explanation: In the output of column description we can see the ‘gradeObtained‘ has the IS_NULLABLE as NO.

Adding a NOT NULL Constraint to an Existing Column

For adding a NOT NULL constraint to an existing column we need to check if the column as any NULL values then update to some value if there exists any NULL value in that column and then use the ALTER command to add the NOT NULL constraint.

Let’s See How to Add a NOT NULL Constraint for a Existing Column.

Query:

UPDATE  studentsMarks
SET studentName = 'NA' WHERE studentName IS NULL

ALTER TABLE studentsMarks
ALTER COLUMN studentName VARCHAR(100) NOT NULL

SELECT * FROM studentsMarks

The update statement first updates the studentNames with NULL as NA and then using the ALTER command the NOT NULL constraint is added.

The Result Looks Like:

Let’s check the column specification whether it is changed to NOT NULLABLE or NOT

EXEC sp_columns   studentsMarks

The Result Looks Like:

Explanation: In the result we can see that the studentName with NULL is first update to ‘NA‘ and then the studentName became not nullable as we can see ‘studentName‘ has IS_NULLABLE as NO.