sdlc.dev

SQL and PostgreSQL path · Shaping the data · Topic 5 of 13

Constraints as correctness

Not started

A constraint is a rule the database checks on every write: this column is never empty, this value is unique, this order belongs to a customer who exists. Validation in the application is still useful for friendly error messages, but it cannot see concurrent requests, and it does not run when someone fixes data with a script. Constraints do.

Why it matters: Application checks race with each other and get bypassed by scripts and migrations; a constraint holds for every write, always.

  1. 1The last line of defence

    NOT NULL, CHECK, UNIQUE, PRIMARY KEY and FOREIGN KEY are checked inside the database, for every statement from every client.

  2. 2Unique beats check-then-insert

    Two requests can both see that an email is free and both insert it; a unique index lets exactly one succeed.

  3. 3Foreign keys need an index on your side

    PostgreSQL indexes the referenced key, not the referencing column; without one, deletes on the parent scan the child table.

  4. 4NULL is not a value

    By default a unique constraint allows many rows with NULL in a constrained column; NULLS NOT DISTINCT changes that.

1

How it works

The explanation, with examples you can run.

The kinds

CREATE TABLE customers (  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  email   text NOT NULL UNIQUE,  country text NOT NULL); CREATE TABLE orders (  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  customer_id bigint NOT NULL REFERENCES customers (id),  status      text   NOT NULL CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),  total_cents integer NOT NULL CHECK (total_cents >= 0),  created_at  timestamptz NOT NULL);

Each rule rejects a bad write with an error that names it:

INSERT INTO orders (customer_id, status, total_cents, created_at) VALUES (999999, 'new', 100, now());ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"DETAIL:  Key (customer_id)=(999999) is not present in table "customers". INSERT INTO orders (customer_id, status, total_cents, created_at) VALUES (1, 'refunded', 100, now());ERROR:  new row for relation "orders" violates check constraint "orders_status_check"

A CHECK on an account balance (balance_cents >= 0) turns an overdraft bug into an error instead of a negative balance. Check constraints only see the row being written; rules that span rows or tables need a unique or foreign key, an exclusion constraint, or a trigger.

Unique constraints and races

The common bug: look for a row, and insert if it is not there. Two requests can both look before either inserts. A unique constraint is backed by a unique index, and the second insert fails with SQLSTATE 23505. Handle that error, or let the database resolve it:

INSERT INTO customers (email, country)VALUES ('user1@example.com', 'GB')ON CONFLICT (email) DO NOTHING;

Watch out for NULLs. Under SQL's rules two NULLs are not equal, so UNIQUE (code, user_id) happily stores ('WELCOME', NULL) twice. Since PostgreSQL 15, UNIQUE NULLS NOT DISTINCT (code, user_id) treats them as equal and rejects the second.

Foreign keys

A foreign key says every orders.customer_id must exist in customers.id. You also choose what happens when the parent row is deleted or its key changes: the default NO ACTION (and RESTRICT) raise an error, CASCADE deletes the children too, SET NULL and SET DEFAULT change the reference. Make that choice deliberately; ON DELETE CASCADE on the wrong relationship deletes a lot.

PostgreSQL does not create an index on the referencing column. Without one, every delete of a customer scans orders to find children, and so do joins on that column. Index it unless the table is tiny.

Adding constraints to a live table

Adding a constraint checks every existing row, holding a lock while it does. For large tables, add foreign keys and checks with NOT VALID (new writes are checked at once), then run ALTER TABLE ... VALIDATE CONSTRAINT, which checks old rows under a weaker lock that does not block writes. For a new unique constraint, build the index with CREATE UNIQUE INDEX CONCURRENTLY and then attach it with ADD CONSTRAINT ... UNIQUE USING INDEX.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.Signup code runs SELECT ... WHERE email = $1, and inserts only if nothing was found. Under load, duplicate accounts appear. What is the robust fix?
  2. 2.Table coupons has UNIQUE (code, user_id). How many rows can hold ('WELCOME', NULL)?
  3. 3.Deleting one customer takes seconds and the plan shows a sequential scan on orders. orders.customer_id references customers.id. What is missing?

0 of 3 right so far. Get all three to finish this topic.

Go deeper