SQL NOT NULL Constraint

Summary: in this tutorial, you will learn how to use the SQL NOT NULL constraint to prevent inserting NULL values into columns.

The  NOT NULL constraint prevents inserting NULL values into a column. In the database world, NULL means unknown or missing information.

When a NOT NULL constraint is applied to a column, if you try to insert a NULL value into or update NULL value from the column, the database engine will reject the change and issue an error.

You can create a  NOT NULL constraint in creating or modifying the table.

Creating SQL NOT NULL constraints

The most common way to create a  NOT NULL constraint is via the column’s definition of the CREATE TABLE statement. For example, the following statement creates a new table named authors:

CREATE TABLE authors(
  author_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  author_name VARCHAR(40) NOT NULL,
  bio VARCHAR(400) NULL
)Code language: SQL (Structured Query Language) (sql)

We’ve applied the NOT NULL constraints to the  author_id and  author_name columns.

If you want to add a  NOT NULL constraint to a column of an existing table, you have to use the  ALTER TABLE statement as follows:

ALTER TABLE table
ALTER COLUMN column NOT NULL;Code language: SQL (Structured Query Language) (sql)

For example, we can add a  NOT NULL constraint to the bio column in Microsoft SQL Server:

ALTER TABLE authors
ALTER COLUMN BIO VARCHAR(400) NOT NULL;Code language: SQL (Structured Query Language) (sql)

In MySQL:

ALTER TABLE authors
MODIFY BIO VARCHAR(400) NOT NULL;Code language: SQL (Structured Query Language) (sql)

In Oracle:

ALTER TABLE authors MODIFY bio NOT NULLCode language: SQL (Structured Query Language) (sql)

Removing SQL NOT NULL constraint

To remove an existing  NOT NULL constraint, you use the ALTER TABLE statement. For example, to remove the  NOT NULL constraint on the bio column, you use the following statement:

In SQL Server:

ALTER TABLE authors 
ALTER COLUMN bio varchar(400) NULLCode language: SQL (Structured Query Language) (sql)

In MySQL:

ALTER TABLE authors
MODIFY BIO VARCHAR(400) NULL;Code language: SQL (Structured Query Language) (sql)

In Oracle:

ALTER TABLE authors
MODIFY (bio NULL)Code language: SQL (Structured Query Language) (sql)

In this tutorial, we have shown you how to apply SQL NOT NULL constraint to prevent adding NULL values to columns of a table.