Constraints and keys

A constraint is a rule the database enforces on every write, from every client, forever. Application validation is useful for friendly error messages; a constraint is what makes the rule true.

The five you use every day

• PRIMARY KEY — identifies a row: unique and not null. One per table; it may span several columns (a composite key).
• UNIQUE — no two rows share the value. Unlike a primary key, the column may be nullable.
• NOT NULL — the value must be present.
• CHECK (condition) — the row is rejected if the condition is false.
• FOREIGN KEY ... REFERENCES parent (key) — every non-null value must exist in the parent table's key.

What each one refuses — and lets through

The setup below defines a small helper, try_sql, that runs a statement and records "ok" or the error message instead of stopping. It is written in PL/pgSQL, PostgreSQL's procedural language, and it is only plumbing, so you can watch seven attempts in one run.

1CREATE TABLE customers (2  id     int PRIMARY KEY,3  email  text UNIQUE,4  credit int CHECK (credit >= 0)5);6CREATE TABLE orders (7  id          int PRIMARY KEY,8  customer_id int NOT NULL REFERENCES customers (id)9);10INSERT INTO customers VALUES (1, 'nino@example.com', 100);11 12-- Helper: run a statement, record 'ok' or the error, carry on.13CREATE TABLE attempts (14  n         serial PRIMARY KEY,15  statement text NOT NULL,16  outcome   text NOT NULL17);18CREATE FUNCTION try_sql(stmt text) RETURNS void LANGUAGE plpgsql AS $$19DECLARE20  result text;21BEGIN22  BEGIN23    EXECUTE stmt;24    result := 'ok';25  EXCEPTION WHEN others THEN26    result := 'error: ' || SQLERRM;27  END;28  INSERT INTO attempts (statement, outcome) VALUES (stmt, result);29END $$;

1SELECT try_sql('INSERT INTO customers VALUES (1, ''ana@example.com'', 50)');2SELECT try_sql('INSERT INTO customers VALUES (2, ''nino@example.com'', 50)');3SELECT try_sql('INSERT INTO customers VALUES (3, ''luka@example.com'', -5)');4SELECT try_sql('INSERT INTO customers VALUES (4, NULL, NULL)');5SELECT try_sql('INSERT INTO customers VALUES (5, NULL, 0)');6SELECT try_sql('INSERT INTO orders VALUES (10, 99)');7SELECT try_sql('INSERT INTO orders VALUES (11, NULL)');8 9SELECT statement, outcome FROM attempts ORDER BY n;

The duplicate id, the duplicate email, the negative credit, the order for customer 99 and the order with no customer are all refused. Two rows are accepted that surprise people:

• A CHECK passes when its condition is unknown. NULL >= 0 is unknown, not false, so a NULL credit gets in. If the value must also be present, add NOT NULL.
• UNIQUE allows several NULLs in PostgreSQL and MySQL, because one unknown is not equal to another. SQL Server's UNIQUE constraint allows only one; PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT for that behaviour.

A foreign key column that is nullable accepts NULL too — "no parent" — which is why the orders.customer_id above is declared NOT NULL.

When the parent is deleted

A foreign key also decides what happens to child rows when their parent goes:

• NO ACTION (the default) or RESTRICT — the delete is refused while children exist.
• ON DELETE CASCADE — the children are deleted too.
• ON DELETE SET NULL — the children stay, with the reference cleared.

1CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);2CREATE TABLE orders (3  id          int PRIMARY KEY,4  customer_id int NOT NULL REFERENCES customers (id) ON DELETE CASCADE5);6CREATE TABLE order_lines (7  id       int PRIMARY KEY,8  order_id int NOT NULL REFERENCES orders (id) ON DELETE CASCADE9);10CREATE TABLE tickets (11  id          int PRIMARY KEY,12  customer_id int REFERENCES customers (id) ON DELETE SET NULL13);14INSERT INTO customers VALUES (1, 'Nino'), (2, 'Ana');15INSERT INTO orders VALUES (10, 1), (11, 1), (12, 2);16INSERT INTO order_lines VALUES (1000, 10), (1001, 10), (1002, 12);17INSERT INTO tickets VALUES (100, 1), (101, 2);

1DELETE FROM customers WHERE id = 1;2 3SELECT 'order_lines' AS child_table, id, order_id AS parent_id FROM order_lines4UNION ALL5SELECT 'orders', id, customer_id FROM orders6UNION ALL7SELECT 'tickets', id, customer_id FROM tickets8ORDER BY child_table, id;

Deleting Nino removed his two orders, then — because order_lines also cascades from orders — the lines of those orders, and kept his support ticket with an empty customer_id. A cascade carries on down every level that is declared CASCADE. That makes cascades convenient and dangerous in equal measure: one DELETE can remove rows in tables the person typing it never thought about.

Natural or surrogate keys

A natural key is a real-world value (an email, a country code); a surrogate key is an id with no meaning. Surrogates never change, which matters because every foreign key copies the key; keep the natural value as a UNIQUE column so duplicates are still refused.

Common mistakes

• Relying on application checks alone. Two requests can each check "email not taken" and both insert; only a UNIQUE constraint stops the second.
• A CHECK on a nullable column with no NOT NULL. The NULL slips past, as the rejections example shows.
• Foreign keys without an index on the child column. The constraint does not create one in PostgreSQL or SQL Server, and every parent delete must then scan the child table.
• Adding a constraint to a table that already breaks it. The ALTER TABLE fails; find the offending rows with a query first.