NOT NULL, UNIQUE and CHECK

Why put rules in the database

Your application probably validates input already. Constraints still matter, because the application is not the only thing that writes data: there are other services, scripts, migrations, admin consoles and the colleague who fixes something by hand at 2 a.m. A constraint applies to every write, whoever makes it.

The practice schema already has several. Each one below was triggered on shop, inside a transaction we rolled back.

NOT NULL: a value is required

INSERT INTO customers (id, name, email, joined_on) VALUES (9, NULL, 'x@example.com', '2025-10-18');

ERROR:  null value in column "name" of relation "customers" violates not-null constraint
DETAIL:  Failing row contains (9, null, x@example.com, null, 2025-10-18).

Make every column NOT NULL unless “unknown” is a genuine, meaningful state for it, as customers.city is.

UNIQUE: no duplicates

customers.email is unique, and order_items has a two-column primary key, which is unique as a pair:

INSERT INTO order_items VALUES (101, 3, 1, 18999.00), (101, 3, 1, 18999.00);

ERROR:  duplicate key value violates unique constraint "order_items_pkey"
DETAIL:  Key (order_id, product_id)=(101, 3) already exists.

CHECK: any rule you can write

CHECK takes a condition that every row must satisfy. The shop uses it for positive prices, positive quantities and a fixed list of order statuses:

INSERT INTO orders (id, customer_id, status, ordered_at) VALUES (113, 1, 'shiped', now());

ERROR:  new row for relation "orders" violates check constraint "orders_status_check"

A typo in a status is exactly the kind of bad data that is easy to write and painful to find later.

The NULL loopholes

CREATE TEMP TABLE badges (code text UNIQUE);
INSERT INTO badges VALUES (NULL), (NULL);    -- INSERT 0 2

CREATE TEMP TABLE prices (price numeric CHECK (price > 0));
INSERT INTO prices VALUES (NULL);            -- INSERT 0 1

The fixes are explicit:

• If NULL must not be allowed, add NOT NULL. That is the only constraint that rejects NULL.
• If at most one row may have a NULL, declare UNIQUE NULLS NOT DISTINCT:

CREATE TEMP TABLE badges2 (code text UNIQUE NULLS NOT DISTINCT);
INSERT INTO badges2 VALUES (NULL), (NULL);

ERROR:  duplicate key value violates unique constraint "badges2_code_key"
DETAIL:  Key (code)=(null) already exists.

Check yourself

1. Why enforce a rule with a constraint when the application already validates it?

   Show answer

   The database enforces a constraint on every write from every source: other services, scripts, migrations and manual fixes, not just the code path that validates.

   I could explain this without looking

2. A column has CHECK (quantity > 0). Can it contain NULL? Why?

   Show answer

   Yes. NULL > 0 evaluates to unknown, and a check constraint only rejects a row when its condition is false. Add NOT NULL to forbid NULL.

   I could explain this without looking

3. How many rows with a NULL value can a UNIQUE column hold by default?

   Show answer

   Any number. By default two NULLs are not considered equal, so they do not conflict. UNIQUE NULLS NOT DISTINCT allows only one.

   I could explain this without looking