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.
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.