NOT NULL

What NOT NULL Does

NOT NULL is a column constraint that forbids the absence-of-value marker NULL from ever being stored in that column. It says nothing about which values are valid — only that some value must be present. A column can be NOT NULL and still accept an empty string '' or 0, because those are values, not the absence of a value.

Declaring It at Column Level

sql
CREATE TABLE employees (
    emp_id    INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email     VARCHAR(150) NOT NULL,
    manager_id INT NULL          -- explicit NULL is the default anyway
);

Every column is nullable unless you say otherwise, so NOT NULL must be spelled out explicitly.

Adding NOT NULL to an Existing Column

This is where dialects diverge sharply — there is no single portable statement.

sql
-- MySQL / MariaDB: repeat the FULL column definition
ALTER TABLE employees MODIFY email VARCHAR(150) NOT NULL;

-- PostgreSQL: a dedicated sub-clause, no need to restate the type
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;

-- SQL Server
ALTER TABLE employees ALTER COLUMN email VARCHAR(150) NOT NULL;

-- Oracle
ALTER TABLE employees MODIFY (email VARCHAR2(150) NOT NULL);

Note the trap in MySQL's MODIFY: if you forget to re-specify the type, length, or an existing DEFAULT, you can silently lose those attributes, because MODIFY replaces the whole column definition, not just the nullability bit.

The Existing-NULLs Problem

Every one of the statements above will fail if the column currently contains even one NULL row, because the engine validates existing data against the new rule before committing the change. The standard remedy is a three-step pattern:

sql
-- 1. Backfill first
UPDATE employees SET email = 'unknown@example.com' WHERE email IS NULL;

-- 2. Now the constraint can be added safely
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;   -- PostgreSQL

Skipping step 1 gives an error like column "email" contains null values (Postgres) or Column 'email' cannot be null (MySQL, during the implicit re-validation on some storage engines).

Removing NOT NULL

sql
ALTER TABLE employees ALTER COLUMN manager_id DROP NOT NULL;   -- PostgreSQL
ALTER TABLE employees MODIFY manager_id INT NULL;              -- MySQL

Edge Cases

• NOT NULL combined with a DEFAULT means: omit the column in INSERT → default kicks in and satisfies the constraint; explicitly insert NULL → constraint violation, DEFAULT does not rescue you.
• A PRIMARY KEY column is always implicitly NOT NULL (plus unique) — you never need to write it twice, though doing so is harmless.
• NOT NULL is a column constraint only; there is no "table-level" form of it, unlike CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY.

Key Takeaways / Q&A

Q: Does `NOT NULL` prevent empty strings? A: No. '' is a zero-length value, not NULL. Use a CHECK if you also need to reject empty strings.

Q: Why does adding `NOT NULL` to a populated column sometimes fail even though you just checked and saw no NULLs? A: Concurrent writes between your check and your ALTER TABLE can insert a NULL in the gap; in high-traffic systems this is handled with a brief lock or by adding the constraint as NOT VALID first (Postgres) then validating separately.

Q: Is `NOT NULL` enforced at INSERT and UPDATE, or only at INSERT? A: Both — any statement that would leave the column NULL after execution is rejected.