SQL NOT NULL Constraint

In this tutorial, we'll learn to use the NOT NULL constraint with the help of examples.

The NOT NULL constraint in a column means that the column cannot store NULL values. For example,

CREATE TABLE Colleges (
  college_id INT NOT NULL,
  college_code VARCHAR(20),
  college_name VARCHAR(50)
);

Here, the college_id and the college_code columns of the Colleges table won't allow NULL values.

Note: The NOT NULL constraint is used to add a constraint table whereas IS NULL and NOT NULL are used with the WHERE clause to select rows from the table.


Remove NOT NULL Constraint

We can also remove the NOT NULL constraint if that is no longer needed. For example,

SQL Server

ALTER TABLE Customers
ALTER COLUMN age INT;

Oracle

ALTER TABLE Customers
MODIFY (age NULL);

MySQL

ALTER TABLE Customers
MODIFY age INT;

PostgreSQL

ALTER TABLE Customer
ALTER COLUMN age DROP NOT NULL;

NOT NULL Constraint With Alter Table

We can also add the NOT NULL constraint to a column in an existing table using the ALTER TABLE command. For example,

SQL Server

ALTER TABLE Customers
ALTER COLUMN age INT NOT NULL;

Oracle

ALTER TABLE Customers
MODIFY age INT NOT NULL;

MySQL

ALTER TABLE Customers
MODIFY COLUMN age INT NOT NULL;

PostgreSQL

ALTER TABLE Customers
ALTER COLUMN age SET NOT NULL;

Here, the SQL command adds the NOT NULL constraint to the column college_id in an existing table.


Now when we try to insert records in a Colleges table without value for college_id, SQL will give us an error. For example,

INSERT INTO Colleges(college_code, college_name)
VALUES ('NYC', "US");

Here, the SQL command gives us an error because we cannot skip the college_id field in a table because of the NOT NULL constraint.


Recommended Readings

Did you find this article helpful?