SQL Not Null Constraint

The NOT NULL constraint enforces that a column cannot accept NULL values. This ensures that a field must always contain a valid entry when inserting or updating a record in a database table.

By default, SQL table columns can store NULL values (which represent missing, unknown, or unassigned data). Applying NOT NULL guarantees data integrity for critical fields like usernames, email addresses, primary keys, or transaction totals.

Basic Syntax & Examples

A NOT NULL constraint is defined at the column level during table declaration.

1. Defining `NOT NULL` During Table Creation

CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    FirstName  VARCHAR(50) NOT NULL,
    LastName   VARCHAR(50) NOT NULL,
    Email      VARCHAR(100) NOT NULL,
    Age        INT -- Permits NULL values by default
);

In this table:

EmployeeID, FirstName, LastName, and Email are compulsory.
Age is optional; omitting it inserts a NULL value without raising an error.

Behavior During Data Insertions & Updates

If an INSERT or UPDATE query attempts to set a NOT NULL column to NULL (or omits a NOT NULL column that lacks a DEFAULT value), the database engine rejects the operation.

Failed Insert Example

-- This query fails because 'LastName' is omitted and holds no default value
INSERT INTO Employees (EmployeeID, FirstName, Email)
VALUES (101, 'Sarah', 'sarah@example.com');

Error Output (Engine Dependent):

ERROR: null value in column "LastName" violates not-null constraint

Adding `NOT NULL` to an Existing Table

To apply a NOT NULL restriction to an existing column, use the ALTER TABLE statement.

Prerequisite: Before altering a column to NOT NULL, you must update or remove any existing NULL values in that column. Otherwise, the engine will throw an error.

-- Step 1: Clean up existing NULL values
UPDATE Employees
SET Age = 0
WHERE Age IS NULL;

-- Step 2: Apply NOT NULL constraint (Syntax varies by RDBMS)

-- SQL Server / MySQL (Requires specifying the full data type)
ALTER TABLE Employees
ALTER COLUMN Age INT NOT NULL;

-- PostgreSQL
ALTER TABLE Employees
ALTER COLUMN Age SET NOT NULL;

-- Oracle
ALTER TABLE Employees
MODIFY Age NOT NULL;

Removing a `NOT NULL` Constraint

To allow a column to store NULL values again:

-- PostgreSQL
ALTER TABLE Employees
ALTER COLUMN Age DROP NOT NULL;

-- SQL Server / MySQL
ALTER TABLE Employees
ALTER COLUMN Age INT NULL;

-- Oracle
ALTER TABLE Employees
MODIFY Age NULL;

Key Considerations

NOT NULL vs. Empty Strings: A NOT NULL constraint prevents NULL entries, but it does allow empty strings ('') or whitespace for character types unless combined with a CHECK constraint.

-- To prevent empty strings in addition to NULL:
Email VARCHAR(100) NOT NULL CHECK (LENGTH(TRIM(Email)) > 0)

Interaction with DEFAULT: Combining NOT NULL with a DEFAULT constraint guarantees that omitting the column during insertion won't cause a failure, as the engine applies the default value instead.
Primary Keys: Primary key columns automatically enforce NOT NULL implicitly across all standard database engines.