MySQL NOT NULL Constraint

MySQL NOT NULL Constraint: Requiring Column Values

The MySQL NOT NULL constraint guarantees that a column can never store a NULL value. If an INSERT or UPDATE tries to omit a NOT NULL column (with no DEFAULT) or set it to NULL, MySQL rejects the statement immediately.

Why NOT NULL Matters

Nullable columns complicate every query with IS NULL checks, COALESCE() wrappers, and index overhead. Making required columns NOT NULL simplifies application code and keeps storage layouts leaner.

Practical SQL Examples

Define NOT NULL directly beside the column type in CREATE TABLE, or enforce it later via ALTER TABLE after backfilling existing NULL rows:

-- Enforce NOT NULL at table creation
CREATE TABLE demo_customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    notes VARCHAR(255) NULL
);

-- Safely backfill before adding NOT NULL to an existing column
UPDATE demo_customers
SET first_name = 'Guest'
WHERE first_name IS NULL;

ALTER TABLE demo_customers
    MODIFY COLUMN first_name VARCHAR(50) NOT NULL;

Common Mistakes and Best Practices

• Backfill Before Running MODIFY COLUMN ... NOT NULL: If even one row contains NULL, ALTER TABLE ... NOT NULL fails in strict SQL mode (Data truncated for column). Always run an UPDATE ... WHERE col IS NULL step first.
• Combine NOT NULL with DEFAULT on New Migration Columns: When adding a required column to a populated table, write ADD COLUMN is_verified TINYINT(1) NOT NULL DEFAULT 0 so existing rows populate automatically.
• Keep NOT NULL During Type Edits: Remember to repeat NOT NULL whenever changing a column's length with MODIFY COLUMN, or MySQL resets it to NULL.

Frequently Asked Questions

Does NOT NULL block empty strings '' or zero 0?

No, '' and 0 are valid non-null values; add a CHECK (TRIM(col) <> '') constraint if empty strings must also be blocked.

Can a PRIMARY KEY column allow NULL?

No, MySQL automatically marks every PRIMARY KEY column as NOT NULL.

Should optional timestamps like deleted_at or shipped_at be NOT NULL?

No, columns representing events that have not happened yet should stay nullable (NULL) so IS NULL accurately represents unshipped or active rows.