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.

SQL NOT NULL constraint

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 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: PHP (php)

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: PHP (php)

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: PHP (php)

In MySQL:

ALTER TABLE authors MODIFY BIO VARCHAR(400) NOT NULL;
Code language: PHP (php)

In Oracle:

ALTER TABLE authors MODIFY bio NOT NULL
Code language: PHP (php)

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) NULL
Code language: PHP (php)

In MySQL:

ALTER TABLE authors MODIFY BIO VARCHAR(400) NULL;
Code language: PHP (php)

In Oracle:

ALTER TABLE authors MODIFY (bio NULL)
Code language: PHP (php)

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