SQL NOT NULL Constraint

Summary: In this tutorial, you’ll learn how to use the SQL NOT NULL constraint to ensure a column cannot have NULL.

Introduction to SQL NOT NULL constraint #

In SQL, NULL means unknown value or missing data. If you define a table column without using any constraint, it will accept NULL by default.

To ensure that the column cannot have NULL, you can use the NOT NULL constraint.

The NOT NULL constraint prevents inserting or updating NULL into a specified column. It is helpful for defining important columns that should never be NULL.

To apply a NOT NULL constraint to a column, you use the following syntax:

column_name datatype NOT NULLCode language: SQL (Structured Query Language) (sql)

For example, the following creates a table called candidates table to store candidates:

CREATE TABLE candidates (
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(512) NOT NULL,
  phone VARCHAR(25) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Try it

The candidates table includes four columns with NOT NULL constraints. It means you cannot insert NULL into any of this column.

The following statement will fail because it attempts to insert NULL into the email and phone columns:

INSERT INTO
  candidates (first_name, last_name, email, phone)
VALUES
  ('John', 'Doe', NULL, NULL);Code language: SQL (Structured Query Language) (sql)

Try it

However, the following insert statement succeeds because it provides valid values for all the NOT NULL columns:

INSERT INTO
  candidates (first_name, last_name, email, phone)
VALUES
  (
    'John',
    'Doe',
    '[email protected]',
    '(408)-111-2222'
  );Code language: SQL (Structured Query Language) (sql)

Try it

Adding a NOT NULL constraint to an existing column #

Typically, you add a NOT NULL constraint to a column when creating the table. Sometimes, you may want to apply a NOT NULL constraint to an existing nullable column.

To do that, you need to follow these steps:

First, update NULL to a non-NULL value using the UPDATE statement:

UPDATE table_name
SET
  column_name = non_null_value
WHERE
  column_name IS NULL;Code language: SQL (Structured Query Language) (sql)

Note that we use the IS NULL operator in the WHERE clause to find the rows with values in the column column_name are NULL.

Second, add the NOT NULL constraint to the column using the ALTER TABLE statement

ALTER TABLE table_name
MODIFY column_name data_type NOT NULL;Code language: SQL (Structured Query Language) (sql)

The following example shows how to add a NOT NULL constraint to an existing column:

First, create a table called interviews to store interviews:

CREATE TABLE interviews (
  candidate_id INT NOT NULL,
  hiring_manager VARCHAR(255) NOT NULL,
  interview_date TIMESTAMP
);Code language: SQL (Structured Query Language) (sql)

Try it

Second, add the NOT NULL constraint to the interview_date column:

ALTER TABLE interviews 
MODIFY interview_date TIMESTAMP NOT NULL;Code language: SQL (Structured Query Language) (sql)

Try it

Summary #

Use the NOT NULL constraint to ensure a column cannot have NULL.

Databases #

Quiz #

Was this tutorial helpful ?