Keys, Constraints and Data Integrity

Description

A database's job isn't only to store data - it's to refuse data that's wrong. Picture a SaaS app three years in: two accounts for Ava@Example.com and ava@example.com, revenue counted twice because one customer has two active subscriptions, order lines pointing at orders that no longer exist, a seats value of -3. Every one of those rules was "enforced by the app" - by the signup form, the billing sync, the admin tool. Then someone ran a script, two requests raced, or a new service wrote to the table directly.

Constraints are rules the database itself enforces, whoever is writing. They're simple to declare, and they're the cheapest insurance a backend has. This lesson goes from the basics to the real-world details:

1. Primary keys - identity columns and UUIDs.
2. NOT NULL, DEFAULT and CHECK, and the NULL loophole in CHECK.
3. UNIQUE with case, NULLs and conditions ("one active subscription").
4. Foreign keys, and what should happen when a parent row is deleted.
5. Modeling relationships so each fact lives in one place.
6. Adding rules to tables that already hold data.

The examples start from customers, orders, order_items and articles; every script ran in the sandbox on PostgreSQL 18.

Keys

Every table gets a primary key - unique and never NULL. For a surrogate key, let the database generate it with an identity column, the SQL-standard replacement for serial:

CREATE TABLE teams (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);
INSERT INTO teams (name) VALUES ('Platform'), ('Payments');

GENERATED ALWAYS refuses ids from the app - ERROR: cannot insert a non-DEFAULT value into column "id" - unless the insert says OVERRIDING SYSTEM VALUE, which catches code that invents ids. GENERATED BY DEFAULT accepts them.

When ids are created outside one database - by several services, by clients offline, or before a row is saved - use UUIDs. Postgres 18's uuidv7() starts with a millisecond timestamp, so newer keys sort after older ones:

SELECT uuid_extract_version(uuidv7()) AS version,
       uuidv7() < uuidv7()           AS increasing;

That order matters to the primary-key index. Random version-4 UUIDs (gen_random_uuid()) land all over the B-tree, splitting pages and spreading writes across the whole index; v7 keys append at the end, like an identity. The tradeoff: a v7 id reveals when it was created. Natural keys - an email, a SKU - make poor primary keys because they change; keep them as UNIQUE columns instead.

NOT NULL, DEFAULT and CHECK

NOT NULL is the cheapest constraint and the most often forgotten. DEFAULT fills a column an insert leaves out. CHECK rejects a row when its condition is false - but NULL makes the condition unknown, and unknown passes:

CREATE TABLE coupons (
    code        text PRIMARY KEY,
    percent_off int CHECK (percent_off BETWEEN 1 AND 100)
);
INSERT INTO coupons VALUES ('SPRING', 20), ('MYSTERY', NULL);

The coupon with no discount got in. When a value is required, say NOT NULL too. A value out of range is refused: INSERT INTO coupons VALUES ('HUGE', 150) fails with ERROR: new row for relation "coupons" violates check constraint "coupons_percent_off_check".

A CHECK can compare columns (CHECK (ends_at > starts_at)) and state rules such as "a free plan has one seat" (CHECK (plan <> 'free' OR seats = 1)). It can't look at other rows or tables - that's what UNIQUE and foreign keys are for.

UNIQUE: case, NULLs and conditions

UNIQUE is enforced by a unique index. Three details decide whether it enforces what you mean.

Case. UNIQUE (email) treats Ava@Example.com and ava@example.com as different values - both rows go in. Index an expression instead:

CREATE TABLE users (
    id    int PRIMARY KEY,
    email text NOT NULL,
    phone text UNIQUE
);
CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email));
INSERT INTO users VALUES (1, 'Ava@Example.com', NULL), (2, 'liam@example.com', NULL);
INSERT INTO users VALUES (3, 'ava@example.com', '+1 512 555 0100');

ERROR: duplicate key value violates unique constraint "users_email_lower_key"

NULLs. In that script, users 1 and 2 both have a NULL phone, and UNIQUE (phone) let them in: NULL isn't equal to anything, not even NULL. Since Postgres 15, NULLS NOT DISTINCT treats NULLs as equal:

CREATE TABLE devices (
    id     int PRIMARY KEY,
    serial text UNIQUE NULLS NOT DISTINCT
);
INSERT INTO devices VALUES (1, NULL), (2, NULL);

ERROR: duplicate key value violates unique constraint "devices_serial_key"

Conditions. "One active subscription per customer, any number of canceled ones" is a partial unique index - only rows matching its WHERE are in it:

CREATE TABLE subscriptions (id int PRIMARY KEY, customer_id int NOT NULL, status text NOT NULL);
CREATE UNIQUE INDEX one_active_subscription ON subscriptions (customer_id)
    WHERE status = 'active';
INSERT INTO subscriptions VALUES (1, 7, 'canceled'), (2, 7, 'canceled'), (3, 7, 'active');
INSERT INTO subscriptions VALUES (4, 7, 'active');

ERROR: duplicate key value violates unique constraint "one_active_subscription"

Foreign keys and ON DELETE

orders.customer_id REFERENCES customers (id) guarantees every order points at a real customer. What happens when the referenced row is deleted is part of the design:

In the example schema, orders use the default and order items cascade. Deleting customer 1:

DELETE FROM customers WHERE id = 1;

ERROR: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"

Deleting order 10 takes its two items with it:

DELETE FROM orders WHERE id = 10;
SELECT order_id, sku FROM order_items ORDER BY order_id, sku;

Cascade where the child can't exist without its parent (an order's lines); refuse where a delete would destroy history (a customer's orders). And index the referencing column yourself: Postgres requires the referenced side to be a primary key or unique - so it has an index - but creates none for orders.customer_id. Here orders has only orders_pkey; without an index on customer_id, every customer delete scans all orders to check for references, and so does every "orders for this customer" page.

Modeling: one fact in one place

The example schema as a diagram - each line is a foreign key, read as "one customer has zero or more orders":

• One-to-many: the "many" side holds the foreign key (orders.customer_id).
• Many-to-many: a join table with a composite primary key - never a comma-separated list. A list can't be joined, indexed or constrained, and searching it goes wrong:

SELECT title FROM articles
WHERE tags LIKE '%sql%'
ORDER BY title;

'%sql%' matches mysql too. With a join table, the pair can't repeat and a link can't point at a missing article or tag:

CREATE TABLE tags (
    id   int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE
);
CREATE TABLE article_tags (
    article_id int NOT NULL REFERENCES articles (id) ON DELETE CASCADE,
    tag_id     int NOT NULL REFERENCES tags (id),
    PRIMARY KEY (article_id, tag_id)
);
SELECT conname, contype FROM pg_constraint WHERE conrelid = 'article_tags'::regclass ORDER BY conname;

Two foreign keys (f) and the composite primary key (p) - and, new in Postgres 18, each NOT NULL is a named constraint (n) too, so it can be added NOT VALID and validated later like the others.

• Allowed values: a short, fixed list fits CHECK (status IN (...)). A list that changes, or carries data of its own (a plan's price), belongs in a lookup table that a foreign key references - adding a plan is then an INSERT, not a schema change.
• Derived values: a generated column can't disagree with its inputs. In Postgres 18 it is virtual by default - computed when read, stored nowhere; add STORED to compute it on write instead:

ALTER TABLE order_items ADD COLUMN unit_price numeric(10,2) NOT NULL DEFAULT 10.00;
ALTER TABLE order_items ADD COLUMN line_total numeric(10,2)
    GENERATED ALWAYS AS (qty * unit_price);
SELECT order_id, sku, line_total FROM order_items ORDER BY order_id, sku;

Adding rules to tables that hold data

A constraint added to an existing table is checked against every row, and the whole statement fails if one breaks it. Order 12 has a total of -5.00:

ALTER TABLE orders ADD CONSTRAINT orders_total_check CHECK (total >= 0);

ERROR: check constraint "orders_total_check" of relation "orders" is violated by some row

So a real migration is usually fix the data, then add the rule. On a large table, split the rule in two:

ALTER TABLE orders ADD CONSTRAINT orders_total_check CHECK (total >= 0) NOT VALID;
UPDATE orders SET total = 0 WHERE total < 0;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;
SELECT conname, convalidated FROM pg_constraint WHERE conname = 'orders_total_check';

NOT VALID enforces the rule on new writes at once (an insert with a total of -1.00 fails) without scanning the existing rows, so it holds its lock only briefly; VALIDATE CONSTRAINT scans later under a lock that doesn't block writes. Foreign keys work the same way. The other everyday cases:

• A column becomes required: backfill it (UPDATE ... WHERE col IS NULL), then SET NOT NULL. ALTER COLUMN ... SET DEFAULT only affects future inserts - it doesn't fill existing NULLs, and SET NOT NULL still fails.
• A column becomes unique: decide which duplicate wins, remove or retire the others, then create the unique index - on a busy table with CREATE UNIQUE INDEX CONCURRENTLY, which must run on its own, outside a transaction.
• Scripts that run everywhere: CREATE ... IF NOT EXISTS and DROP ... IF EXISTS let one migration run safely on databases in different states.

Which constraint enforces which rule?

Where it breaks

• CHECK passes NULL - add NOT NULL when a value is required.
• UNIQUE is case-sensitive and lets NULLs repeat - index lower(email); use NULLS NOT DISTINCT if one NULL is the limit.
• A foreign key without an index on the referencing column - slow deletes and slow joins.
• CASCADE on a relationship that holds history - one delete removes years of orders.
• A comma-separated list where a join table belongs.
• Adding a rule before cleaning the data, or expecting a new DEFAULT to fill old NULLs.

Tradeoffs

• Constraints vs validation in the app: keep both. The app gives friendly messages; the database guarantees the rule for every writer, script and race.
• Identity vs UUIDv7: identity ids are compact (8 bytes) and fast; UUIDv7 can be generated anywhere and doesn't reveal how many rows you have, at 16 bytes and a visible creation time.
• CHECK list vs lookup table vs enum type: CHECK for short fixed lists, a lookup table for lists that change or carry data; an ENUM type is compact but you can't remove a value from it.

Going further. The deep dive (next tab) covers the locks each schema change takes and how to change a busy table safely, deferrable constraints, exclusion constraints for overlapping bookings, how random UUIDs bloat an index, tenant-safe foreign keys, soft deletes, and when to break normalization on purpose.

Check yourself

1. percent_off int CHECK (percent_off BETWEEN 1 AND 100). Can a row have a NULL percent_off?

2. Each customer may have many addresses but at most one default one. Which constraint enforces that?

3. ALTER TABLE accounts ALTER COLUMN country SET NOT NULL fails in production. What's the safe sequence?

Key takeaways

• Every table has a primary key: an identity column, or uuidv7() when ids are made outside the database. Keep natural keys as UNIQUE columns.
• CHECK rejects only false - pair it with NOT NULL. UNIQUE is case-sensitive and NULL-tolerant; use expression, partial and NULLS NOT DISTINCT indexes to say exactly what's unique.
• Choose ON DELETE per relationship, and index foreign-key columns yourself.
• Model many-to-many with a join table; use lookup tables for value lists that change.
• On live tables: clean the data, then add the rule - with NOT VALID + VALIDATE and CONCURRENTLY when the table is big.