SQL NOT NULL Constraint

The NOT NULL constraint in SQL is used to ensure that a column in a table does not contain any NULL values. This helps to maintain data integrity by preventing records with missing or undefined values.

NOT NULL: Definition and Usage

By default, most SQL database columns allow NULL values. The NOT NULL constraint is applied to enforce the rule that every row must have a value in the specified column. This is essential for columns that are critical for identifying records (like primary keys) or for situations where the absence of a value is not meaningful.

Adding NOT NULL Constraints

You can add NOT NULL constraints when creating a new table (using CREATE TABLE) or to an existing table (using ALTER TABLE). The exact syntax might vary slightly between different database systems (MySQL, PostgreSQL, SQL Server, etc.).

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

Creates a table named 'Persons'. You cannot insert rows or update existing rows without providing values for 'ID', 'LastName', and 'FirstName'. Attempting to do so will result in a database error.

Adding a NOT NULL Constraint After Table Creation

This adds a NOT NULL constraint to the 'Age' column of an existing 'Persons' table. The syntax for this varies slightly between different database systems (MySQL, SQL Server, PostgreSQL, etc.).

Syntax (MySQL, Oracle 10g and later)


ALTER TABLE Persons MODIFY Age INT NOT NULL;

Syntax (SQL Server, MS Access)


ALTER TABLE Persons ALTER COLUMN Age INT NOT NULL;

Output

Adds a NOT NULL constraint to the 'Age' column. Attempts to insert new rows or update existing rows with a NULL value for 'Age' will result in a database error. Note that any existing `NULL` values in the 'Age' column would also cause an error unless handled before applying the constraint.