SQL NOT NULL Constraint

The NOT NULL constraint in SQL is used to enforce data integrity by ensuring that a column in a table cannot contain NULL values (meaning empty or undefined values).

NOT NULL Constraint: Definition and Usage

By default, most SQL columns allow NULL values. The NOT NULL constraint prevents this, making sure that every row in the table has a value for that particular column. This is particularly important for columns that are crucial for identifying records (like primary keys) or for those columns where a value is always expected.

Adding NOT NULL Constraints

You can add NOT NULL constraints when you create a table (using CREATE TABLE) or after the table has already been created (using ALTER TABLE). The syntax may vary slightly depending on your database system.

NOT NULL Constraint on CREATE TABLE

This example creates a 'Persons' table where 'ID', 'LastName', and 'FirstName' cannot have NULL values.

Syntax


CREATE TABLE Persons (
  ID INT NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255) NOT NULL,
  Age INT
);

Output

A table named 'Persons' is created. Attempts to insert a new row or update an existing row without providing a value for 'ID', 'LastName', or 'FirstName' will result in an error.

Adding a NOT NULL Constraint After Table Creation

This adds a NOT NULL constraint to an existing column. (The syntax varies slightly based on the database system.)

Syntax (SQL Server, MS Access)


ALTER TABLE Persons
ALTER COLUMN Age INT NOT NULL;

Syntax (MySQL, Oracle prior to 10g)


ALTER TABLE Persons
MODIFY COLUMN Age INT NOT NULL;

Syntax (Oracle 10g and later)


ALTER TABLE Persons
MODIFY Age INT NOT NULL;

Output

Adds a NOT NULL constraint to the 'Age' column. Any attempt to insert a new row or update an existing row without a value for 'Age' will now produce an error. Existing rows with a NULL value in the 'Age' column will also cause an error unless the NULL values are handled first.