SQL NOT NULL Constraint

-- create table with NOT NULL constraint
CREATE TABLE Colleges (
  college_id INT NOT NULL,
  college_code VARCHAR(20),
  college_name VARCHAR(50)
);

NOT NULL Constraint Syntax

The syntax of the SQL NOT NULL constraint is:

CREATE TABLE table_name (
  column_name data_type NOT NULL
);

Here,

• table_name is the name of the table to be created
• column_name is the name of the column where the constraint is to be implemented
• data_type is the data type of the column such as INT, VARCHAR, etc.

Note: The NOT NULL constraint is used to add a constraint to a table column 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 Colleges
ALTER COLUMN college_id INT;

Oracle

ALTER TABLE Colleges
MODIFY (college_id NULL);

MySQL

ALTER TABLE Colleges
MODIFY college_id INT;

PostgreSQL

ALTER TABLE Colleges
ALTER COLUMN college_id DROP NOT NULL;

Here, we have used the ALTER TABLE command in various database systems to remove the NOT NULL constraint from the college_id column.

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 Colleges
ALTER COLUMN college_id INT NOT NULL;

Oracle

ALTER TABLE Colleges
MODIFY college_id INT NOT NULL;

MySQL

ALTER TABLE Colleges
MODIFY COLUMN college_id INT NOT NULL;

PostgreSQL

ALTER TABLE Colleges
ALTER COLUMN college_id SET NOT NULL;

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

Error Due to NOT NULL Constraint

We must enter a value into columns with the NOT NULL constraint. Otherwise, SQL will give us an error.

For example, the college_id column of our Colleges table has the NOT NULL constraint. So, we will get an error if we enter records into the table without supplying a value to college_id.

-- gives error due to NOT NULL constraint
INSERT INTO Colleges (college_code, college_name)
VALUES ('NYC', "US");

Recommended Readings

• SQL Constraints
• SQL INSERT INTO
• SQL PRIMARY KEY
• SQL FOREIGN KEY