Usage of the NOT NULL Constraint

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

CREATE DATABASE w3wiki

Step 2: Use the GFG Database.

USE w3wiki

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 :

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:

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:

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.

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.