Constraints and normalization
Let the schema refuse bad data: keys, CHECK, UNIQUE, foreign-key actions, and the normal forms that stop one fact being stored twice.
After this lesson you can
- Pick the constraint that enforces a given business rule
- Choose a foreign-key ON DELETE action and predict its effect
- Explain 1NF, 2NF and 3NF with an example of the anomaly each prevents
- Find the existing rows that would stop a new constraint from being added
Application code checks data on the way in; constraints check it always, for every client, script and migration, inside the same transaction as the write. A rule the database can enforce belongs in the schema.
The constraint toolbox
PRIMARY KEY— unique and not null; one per table.UNIQUE— no two rows share the value. NULLs are not considered equal, so many rows may hold NULL unless you writeUNIQUE NULLS NOT DISTINCT(PostgreSQL 15+).NOT NULLandCHECK (expr)— aCHECKpasses when the expression is true or NULL, soCHECK (price > 0)accepts a NULL price.REFERENCES(foreign key) — the value must exist in the parent's primary key or unique column.
Each violation has its own SQLSTATE, which is what application code should
match on rather than the message text. The example uses the attempt()
helper from its setup to show each one without aborting.
CREATE FUNCTION attempt(stmt text) RETURNS text LANGUAGE plpgsql AS $$BEGIN EXECUTE stmt; RETURN 'ok';EXCEPTION WHEN OTHERS THEN RETURN SQLSTATE || ': ' || SQLERRM;END $$;CREATE TABLE departments (id int PRIMARY KEY, name text NOT NULL);CREATE TABLE employees ( id int PRIMARY KEY, email text NOT NULL UNIQUE, dept_id int REFERENCES departments (id), salary numeric(10,2) CHECK (salary > 0));CREATE TABLE tags (code text UNIQUE);CREATE TABLE badges (code text UNIQUE NULLS NOT DISTINCT);INSERT INTO departments VALUES (1, 'Engineering');INSERT INTO employees VALUES (1, 'nino@example.com', 1, 5000);SELECT label, attempt(stmt) AS outcomeFROM (VALUES (1, 'duplicate email', $$INSERT INTO employees VALUES (2, 'nino@example.com', 1, 100)$$), (2, 'missing email', $$INSERT INTO employees VALUES (3, NULL, 1, 100)$$), (3, 'unknown dept', $$INSERT INTO employees VALUES (4, 'ana@example.com', 99, 100)$$), (4, 'negative salary', $$INSERT INTO employees VALUES (5, 'luka@example.com', 1, -1)$$), (5, 'NULL salary', $$INSERT INTO employees VALUES (6, 'mari@example.com', 1, NULL)$$), (6, 'two NULL tags', $$INSERT INTO tags VALUES (NULL), (NULL)$$), (7, 'two NULL badges', $$INSERT INTO badges VALUES (NULL), (NULL)$$)) AS t(n, label, stmt)ORDER BY n;What a foreign key does on delete
ON DELETE NO ACTION (the default) and RESTRICT refuse to delete a parent
that still has children. CASCADE deletes the children too; SET NULL
clears their reference. PostgreSQL does not index the referencing
column for you; without an index, deleting a parent scans the child table
to check for references.
CREATE FUNCTION attempt(stmt text) RETURNS text LANGUAGE plpgsql AS $$BEGIN EXECUTE stmt; RETURN 'ok';EXCEPTION WHEN OTHERS THEN RETURN SQLSTATE || ': ' || SQLERRM;END $$;CREATE TABLE orders (id int PRIMARY KEY);CREATE TABLE order_lines ( order_id int NOT NULL REFERENCES orders (id) ON DELETE CASCADE, line_no int NOT NULL, PRIMARY KEY (order_id, line_no));CREATE TABLE invoices (id int PRIMARY KEY, order_id int NOT NULL REFERENCES orders (id));INSERT INTO orders VALUES (1), (2);INSERT INTO order_lines VALUES (1, 1), (1, 2), (2, 1);INSERT INTO invoices VALUES (100, 2);CREATE TEMP TABLE outcome ASSELECT attempt('DELETE FROM orders WHERE id = 1') AS delete_order_1, attempt('DELETE FROM orders WHERE id = 2') AS delete_order_2; SELECT delete_order_1, delete_order_2, (SELECT string_agg(order_id || '/' || line_no, ', ' ORDER BY order_id, line_no) FROM order_lines) AS lines_left, (SELECT count(*) FROM pg_indexes WHERE tablename = 'invoices') AS indexes_on_invoicesFROM outcome;Normalization in one paragraph per form
Normalization stores each fact once, so it cannot disagree with itself.
- 1NF: every column holds one value of its type, with no repeating
groups like
phone1, phone2, phone3. - 2NF: with a composite key, no column depends on only part of it.
In
order_lines(order_id, line_no, customer_name, ...)the customer depends onorder_idalone, so it belongs inorders. - 3NF: no column depends on another non-key column.
citystored next topostcoderepeats what the postcode already determines.
The cost of skipping it is the update anomaly: change the fact in one row, miss another, and the database now holds two answers.
-- Denormalized: the customer's email is repeated on every order.CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, customer_email text NOT NULL);INSERT INTO orders VALUES (1, 7, 'nino@old.example'), (2, 7, 'nino@old.example'), (3, 8, 'ana@example.com'), (4, 7, 'nino@old.example');-- Nino changes her email; the update only touches her latest order.UPDATE orders SET customer_email = 'nino@new.example' WHERE id = 4; SELECT customer_id, count(DISTINCT customer_email) AS versions, string_agg(DISTINCT customer_email, ', ') AS emailsFROM ordersGROUP BY customer_idHAVING count(DISTINCT customer_email) > 1;Denormalizing on purpose (a cached total, a reporting table) is fine when something keeps the copy in sync and you can say what that is.
Common mistakes
- Assuming
CHECK (qty > 0)rejects NULL. It does not; addNOT NULL. - Expecting
UNIQUE (email)to allow only one NULL email; it allows any number unless declaredNULLS NOT DISTINCT. - Adding a foreign key and forgetting to index the referencing column, then wondering why deleting one parent row is slow.
- Storing a customer's name on every order line "for convenience" and later updating only some of them, as the anomaly example shows.
What interviewers ask
- What is the difference between 2NF and 3NF? 2NF removes dependencies on part of a composite key; 3NF removes dependencies between non-key columns.
- When would you deliberately denormalize? For read-heavy reporting or a cached aggregate, with a named mechanism (trigger, job, materialized view) that keeps the copy correct.
- Why enforce rules in the database when the API already validates? Every writer is covered, and the check is atomic with the write.
Try it yourself
2 visible tests · 2 hidden testsTable products(id, sku, price, discount_pct) has no constraints yet
apart from its primary key, and sku, price and discount_pct are
all nullable. You are about to add:
UNIQUE (sku)(plain, notNULLS NOT DISTINCT)CHECK (price > 0)CHECK (discount_pct BETWEEN 0 AND 100)
Return one row per existing row and constraint it would violate, as
id and problem: 'duplicate_sku' (report every row whose sku
another row also has), 'bad_price' or 'bad_discount'. A row can
appear more than once. Judge NULLs exactly as PostgreSQL would when
adding these constraints. Order by id, then problem.
CREATE TABLE products (id int PRIMARY KEY, sku text, price numeric(10,2), discount_pct int);INSERT INTO products VALUES (1, 'TEA-1', 4.00, 10), (2, 'CUP-1', 0.00, 0), (3, 'TEA-1', 5.00, NULL), (4, NULL, NULL, 150), (5, NULL, 3.00, 100);Sign up to run the tests, check the hidden ones and save your progress. Sign up