NOT NULL Constraint

Prevent NULL values from being stored in a column with the NOT NULL constraint.

Overview

The NOT NULL constraint rejects any attempt to store NULL in a column either through INSERT or UPDATE. It is the most common constraint and one of the most important for data quality. When a column should always have a meaningful value (an email address, a creation timestamp, a product name), mark it NOT NULL. Plan your NOT NULL usage carefully: adding NOT NULL to an existing column with NULL values requires either a default value or a manual UPDATE to fill the NULLs first. Without a DEFAULT, adding NOT NULL to a column that already has rows will fail unless all rows have a non-NULL value.

Key Points

•NOT NULL prevents NULL from being stored; INSERT/UPDATE fails if NULL is provided
•NOT NULL cannot be set on a column that already has NULL values without updating them first
•Add NOT NULL with a default: ADD COLUMN col TYPE NOT NULL DEFAULT val
•Remove it with: ALTER TABLE t ALTER COLUMN col DROP NOT NULL
•NULL is different from 0, empty string, or FALSE it means "absent"

Examples

NOT NULL in table definition

CREATE TABLE products (
  id    SERIAL        PRIMARY KEY,
  name  TEXT          NOT NULL,
  price NUMERIC(10,2) NOT NULL
);

-- This fails:
INSERT INTO products (name, price) VALUES (NULL, 9.99);
-- Error: null value in column "name" violates not-null constraint

Adding NOT NULL to existing column

-- 1. Fill NULLs first
UPDATE users SET phone = 'unknown' WHERE phone IS NULL;

-- 2. Then add the constraint
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

NOT NULL Constraint

Overview

Key Points

Examples

Exercises

Knowledge Check

You have an existing column "code TEXT" with some NULL values. What must you do before adding NOT NULL?