SQL NOT NULL Constraint Example

The constraint NOT NULL defines a column to not accept NULL values.

  • We cannot insert a new row without providing a non-null value to such a column.
  • A null value or simply NULL cannot be zero (0), space, blank, or a zero-length character '' which contains blank.

In the below example, the columns id, name, and phone are defined to have NOT NULL values.

Run this on IDE

CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(30) NOT NULL,
    birth_date DATE,
    phone VARCHAR(15) NOT NULL
);

In case the table already exists, it returns an error message, which can be avoided by including the keyword IF NOT EXISTS as shown below.

CREATE TABLE IF NOT EXISTS persons (
    id INT NOT NULL,
    name VARCHAR(30) NOT NULL,
    birth_date DATE,
    phone VARCHAR(15) NOT NULL
);

Overall

We now know when and how to use the NOT NULL constraint.

Related Links