SQL NOT NULL

What does the SQL NOT NULL constraint do?

NOT NULL is a column constraint that prohibits NULL values, guaranteeing every row stores a non-NULL entry in that column.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NOT NULL: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Google BigQuery, Snowflake, Redshift, and virtually all ANSI-compliant databases

SQL NOT NULL Full Explanation

NOT NULL is a fundamental integrity constraint defined at either column level or table level. When a column is declared NOT NULL, the database rejects any INSERT or UPDATE that attempts to put a NULL into the column. The constraint is enforced immediately unless the transaction is rolled back. Because PRIMARY KEY implicitly includes NOT NULL, adding NOT NULL to a primary-key column is redundant but harmless. Dropping or adding NOT NULL on an existing column requires that existing data already satisfy (or be cleaned to satisfy) the rule, otherwise the DDL fails. Some engines let you defer enforcement until commit, but most enforce it row-by-row. NOT NULL does not prevent empty strings in text columns, and does not auto-fill a default value unless DEFAULT is separately specified.

SQL NOT NULL Syntax

-- Column-level declaration
CREATE TABLE table_name (
    column_name data_type NOT NULL,
    ...
);

-- Table-level using CONSTRAINT (supported in most dialects)
CREATE TABLE table_name (
    column_name data_type,
    CONSTRAINT nn_column_name NOT NULL (column_name)
);

-- Add to existing column
ALTER TABLE table_name
    ALTER COLUMN column_name SET NOT NULL;

-- Remove constraint
ALTER TABLE table_name
    ALTER COLUMN column_name DROP NOT NULL;

SQL NOT NULL Parameters

Example Queries Using SQL NOT NULL

-- 1. Create table with NOT NULL
CREATE TABLE customers (
    id          SERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Attempt to insert a NULL (will fail)
INSERT INTO customers (id, email) VALUES (1, NULL);

-- 3. Add NOT NULL to an existing column
ALTER TABLE orders
    ALTER COLUMN status SET NOT NULL;

Expected Output Using SQL NOT NULL

Table is created. 2. Database returns an error similar to "null value in column \"email\" violates not-null constraint" and the row is not inserted. 3. ALTER succeeds only if every existing row already has a non-NULL value in orders.status; otherwise it errors.

Use Cases with SQL NOT NULL

Guarantee that essential identifiers like email, username, or order_status are always present
Enforce business rules where absence of a value is invalid
Simplify query logic by avoiding extra IS NULL checks
Combine with UNIQUE to create candidate keys

Common Mistakes with SQL NOT NULL

Assuming NOT NULL prevents empty strings; it only blocks true NULLs
Forgetting to supply a DEFAULT when converting a nullable column to NOT NULL, causing DDL to fail on existing NULLs
Believing PRIMARY KEY without NOT NULL is allowed (all dialects implicitly add NOT NULL)
Attempting to drop NOT NULL on columns that are part of a primary key (usually disallowed)

First Introduced In

SQL-86 (first ANSI SQL standard)

Frequently Asked Questions

What is the difference between NOT NULL and NULL?

NOT NULL is a rule that forbids NULLs in a column, while NULL is simply a marker meaning “no value.”

Can I add NOT NULL to a column that already contains NULLs?

Not until the NULLs are removed or replaced. Otherwise the ALTER TABLE statement errors out.

Does NOT NULL automatically create an index?

No. You must explicitly add a PRIMARY KEY or UNIQUE constraint or create an index if you need one.

How do I drop a NOT NULL constraint?

Use `ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;` (syntax varies slightly by dialect).