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 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.
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:
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:
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.
Contact Us