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 constraintThe  NOT NULL constraint prevents inserting NULL values into a column. In database, 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:

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:

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

In MySQL:

In Oracle:

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 from the bio column, you use the following statement:

In SQL Server:

In MySQL:

In Oracle:

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