Constraints and NULL

A bug in Pedal's app once created a rental that ended before it started, and another for bike id 0, which does not exist. Code will always have bugs. Constraints are rules the database itself enforces, so bad rows are refused no matter which program sends them.

The rule

Let the database refuse bad data; treat NULL as "unknown", not zero.

PostgreSQL's constraints:

NOT NULL: the column must have a value.
UNIQUE: no two rows share this value (or combination).
PRIMARY KEY: UNIQUE plus NOT NULL; the row's identity. One per table.
FOREIGN KEY (REFERENCES): the value must exist in another table.
CHECK: any true-or-false rule on the row.
EXCLUDE: no two rows may overlap by some operator, such as two bookings of one bike at the same time.

CREATE TABLE rentals (
  rental_id  bigint PRIMARY KEY,
  member_id  bigint NOT NULL REFERENCES members,
  bike_id    bigint NOT NULL REFERENCES bikes,
  started_at timestamptz NOT NULL,
  ended_at   timestamptz,
  CHECK (ended_at IS NULL OR ended_at > started_at)
);

ended_at may be NULL, meaning "not known yet": the ride is still going. NULL is not zero and not an empty string, and it follows three-valued logic: any comparison with NULL gives unknown, not true or false.

ended_at = NULL is never true. Use ended_at IS NULL.
WHERE keeps only rows where the condition is true, so unknown rows drop out.
A CHECK passes unless the result is false, so NULL slips through a check. That is why the check above spells out the NULL case.
UNIQUE allows many NULLs by default, because two unknowns are not known to be equal. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to forbid that.

The shortcut

For every column, say its rule out loud as a sentence, then find the constraint that says the same thing. For every nullable column, ask "What does NULL mean here?" If you cannot answer, make it NOT NULL.

Guarding the rentals tableStep 1 of 4

"Every rental has a member who exists"

member_id NOT NULL REFERENCES members.

Drills

Answer fast — trust the rule.

quick drill

Which query finds rides still in progress?

quick drill

A table has CHECK (fare > 0) and a row arrives with fare NULL. What happens?

quick drill

What does PRIMARY KEY add that UNIQUE alone does not?

quick drill

Which constraint stops one bike from having two rentals whose time ranges overlap?

Remember

Let the database refuse bad data; treat NULL as unknown, not zero.
Say each column's rule as a sentence and pick the constraint that matches.
Test with IS NULL, and remember that NULL slips past CHECK and UNIQUE.

Constraints and NULL

Constraints and NULL

The rule

The shortcut

"Every rental has a member who exists"

Drills

Remember

Practice — level up