Integrity constraints

What you will be able to do

Make PostgreSQL itself refuse bad rows from any program: a duplicate customer, a message for a missing ticket, a priority that does not exist. Then keep each fact in one place, and copy one only when you have a reason.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• The help desk’s entities, keys and relationships, and the three orphan messages, from Entities and relationships.
• What a constraint is, and how SQLite needed PRAGMA foreign_keys = ON, from Relational concepts and From SQLite to PostgreSQL.
• Migrations as numbered SQL files, each applied once, in order, from From SQLite to PostgreSQL.

What you need on your computer

• The project after the last lesson, with docs/schema.md. python -m pytest prints 34 passed.
• PostgreSQL running, with the small data loaded.

This lesson adds two migrations, 005_integrity_constraints.sql and 006_ticket_message_counts.sql, and one test file, tests/test_schema.py.

The counter at the post office

You send a parcel at the post office. The clerk looks at the form before she takes the parcel. The address box is empty: she gives the form back. The weight is 40 kg, and the limit is 30 kg: she says no. The postcode does not exist: she says no.

She checks every form at the counter, for every customer, before anything enters the system. Nobody has to hope that each sender checked the form at home. A refused form leaves no half-sent parcel behind. Either the parcel is in the system with a correct form, or it is not there at all.

The problem: the database accepts anything

Right now, the help desk’s tables have primary keys and nothing else. Try five mistakes in psql. BEGIN and ROLLBACK around them undo everything in between, so your data stays as it is. Transactions explains how.

BEGIN;
INSERT INTO customers (customer_id, name, email, segment, joined_on)
VALUES ('C-0041', 'Olga Olsen', 'Olga.Olsen@Example.com', 'home', '2026-10-09');
INSERT INTO messages (ticket_id, author, body) VALUES ('T-99999', 'agent', 'Hello');
UPDATE tickets SET priority = 4 WHERE ticket_id = 'T-30002';
UPDATE tickets SET status = 'closed' WHERE ticket_id = 'T-30002';
INSERT INTO tickets (ticket_id, subject) VALUES ('30003', '');
SELECT ticket_id, customer_id, priority, status, closed_at
FROM tickets WHERE ticket_id IN ('T-30002', '30003');
ROLLBACK;

BEGIN
INSERT 0 1
INSERT 0 1
UPDATE 1
UPDATE 1
INSERT 0 1
 ticket_id | customer_id | priority | status | closed_at
-----------+-------------+----------+--------+-----------
 30003     |             |          |        |
 T-30002   | C-0022      |        4 | closed |
(2 rows)

ROLLBACK

Every statement worked, and every one was wrong:

• A second account for Olga Olsen. Her email is already in C-0001, in lower case. Now the help desk has two customers with one address.
• A message for T-99999, a ticket that does not exist: a fourth orphan.
• Priority 4. The help desk has priorities 1 to 3. No report or rule knows what 4 means.
• A closed ticket with no closing time. Reports of “time to close” now divide by nothing.
• A ticket called 30003 with no customer, an empty subject, and no status.

Sam asks: “But the API checks its input. Why does it matter?” The API checks one door. The loader, a script, psql, a report job and next year’s second service all write to the same tables. None of them go through the API. Data stays for years; programs change. A rule in the database protects the data from every writer, for as long as the data lives.

The idea: constraints

A constraint is a rule in the table’s definition that the database checks on every write. If a write breaks a rule, PostgreSQL refuses the whole statement, saves nothing of it, and names the rule in the error. It is the clerk at the counter.

PostgreSQL has five kinds, plus defaults:

• NOT NULL: the column must have a value. A ticket without a customer is refused.
• CHECK (…): a condition on the values of one row, for example priority BETWEEN 1 AND 3.
• UNIQUE: no two rows have the same value. A unique index can also use an expression, such as lower(email).
• PRIMARY KEY: unique and not null together: the identifier of the last lesson.
• FOREIGN KEY … REFERENCES: the value must exist in another table’s key. This stops orphans, and it also says what happens when the other row is deleted.
• DEFAULT is not a rule, but it helps: a new ticket gets status = 'open' and created_at = now() when the program does not send them.

Here is a tiny worked example: the rules that ticket T-30002 must pass, with its real values.

Column	T-30002’s value	Rule (name in the database)	A value it refuses
ticket_id	T-30002	tickets_id_format: T- and 5 or 6 digits	30003
customer_id	C-0022	NOT NULL, and tickets_customer_fk: the customer exists	empty, or C-9999
priority	1	tickets_priority_range: 1 to 3	4
status, closed_at	open, empty	tickets_closed_at_matches_status	closed with no closing time
subject	Charged twice for order LK-182074	tickets_subject_not_blank	' '

The database checks the rules of a write one after another. The first rule that fails stops the write:

Every write passes the table's rules. The first rule that fails stops the write, and the error names the rule. The four examples are real errors from this lesson.

Text description of the diagram

A write goes through four checks in a row. NOT NULL fails when a required value is missing, for example when a new ticket has no customer_id. CHECK fails when a value breaks a rule of the row, for example priority = 4 breaks tickets_priority_range. UNIQUE fails when the value is in another row already, for example Olga.Olsen@Example.com breaks customers_email_unique. FOREIGN KEY fails when the row points to nothing, for example a message for T-99999 breaks messages_ticket_fk. A write that passes all four is saved, for every program that reads the table.

A name such as tickets_priority_range is worth the typing. When the rule fails, the error message names it, and the name says what went wrong.

Larkfield’s rules: migration 005

Mei writes the rules as a migration, so every copy of the database gets the same rules. Start where the last lesson ended: the three orphan messages. She writes a first draft of migrations/005_integrity_constraints.sql with only one rule:

migrations/005_integrity_constraints.sql (first draft)

ALTER TABLE messages
    ADD CONSTRAINT messages_ticket_fk FOREIGN KEY (ticket_id)
    REFERENCES tickets (ticket_id) ON DELETE CASCADE;

Until now, scripts.load applied the migrations for you. The project’s migration runner, ticket_api/migrate.py, can also run alone. It applies every file in migrations/ that has not run yet:

python -m ticket_api.migrate

psycopg.errors.ForeignKeyViolation: insert or update on table "messages" violates foreign key constraint "messages_ticket_fk"
DETAIL:  Key (ticket_id)=(T-29994) is not present in table "tickets".

Only the last two lines of the error are shown; above them is a Python traceback. A new constraint must hold for the rows that exist already, so PostgreSQL checked every message. The first orphan, T-29994, stopped the migration. Each migration runs in one transaction, so nothing of the file stayed: python -m ticket_api.migrate --status still shows pending 005_integrity_constraints.

Mei must decide what to do with the three rows. She could delete them. But they are someone’s data, and “somebody deleted the tickets by hand” is a guess. So the first part of the real 005 moves them to a table of their own, for a person to check:

migrations/005_integrity_constraints.sql (part 1)

CREATE TABLE orphaned_messages (LIKE messages);
INSERT INTO orphaned_messages
SELECT m.* FROM messages m
WHERE NOT EXISTS (SELECT 1 FROM tickets t WHERE t.ticket_id = m.ticket_id);
DELETE FROM messages m
WHERE NOT EXISTS (SELECT 1 FROM tickets t WHERE t.ticket_id = m.ticket_id);

LIKE messages copies the columns of messages. The INSERT keeps the orphans, and the DELETE removes them from messages. Then the foreign key can be added.

The rest of 005 adds the rules table by table. Here are the parts that need a word of explanation:

migrations/005_integrity_constraints.sql (parts of 2 and 3)

ALTER TABLE customers
    ALTER COLUMN email SET NOT NULL,
    ADD CONSTRAINT customers_id_format CHECK (customer_id ~ '^C-[0-9]{4,5}$'),
    ADD CONSTRAINT customers_segment_known CHECK (segment IN ('home', 'trade', 'business'));
-- "Ana@Example.com" and "ana@example.com" are the same address.
CREATE UNIQUE INDEX customers_email_unique ON customers (lower(email));

ALTER TABLE tickets
    ALTER COLUMN status SET DEFAULT 'open',
    ADD CONSTRAINT tickets_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    ADD CONSTRAINT tickets_priority_range CHECK (priority BETWEEN 1 AND 3),
    -- A ticket has a closing time exactly when it is resolved or closed.
    ADD CONSTRAINT tickets_closed_at_matches_status
        CHECK ((status IN ('resolved', 'closed')) = (closed_at IS NOT NULL));

• ~ '^C-[0-9]{4,5}$' is a regular expression: C-, then 4 or 5 digits, and nothing else. The large data has customers up to C-20000, so 5 digits are needed.
• UNIQUE on lower(email), not on email. A plain UNIQUE (email) would accept Olga.Olsen@Example.com next to olga.olsen@example.com, because the two texts differ. The index stores every email in lower case, and refuses a second copy.
• tickets_closed_at_matches_status compares two true-or-false values. “Resolved or closed” must be true exactly when “has a closing time” is true. An open ticket with a closing time fails, and a closed ticket without one fails.

The cost_usd column of ai_runs keeps its type, real, in this module. That type rounds numbers, and Migrations changes it, with the reason.

What happens to the children when a ticket is deleted

A foreign key also answers a second question: if the ticket goes, what happens to the rows that point to it? Each answer is a business decision, so 005 gives each table its own:

One delete, four rules. The foreign key of each child table says what happens to its rows when their ticket is deleted.

Text description of the diagram

A delete of ticket T-30001 reaches four tables. messages has ON DELETE CASCADE, so the ticket’s messages are deleted too. ticket_embeddings has ON DELETE CASCADE, so its vector is deleted too. ai_runs has ON DELETE SET NULL, so the runs stay, their ticket_id becomes empty, and the cost stays in the monthly total. attachments has no action, the default. So the delete is refused while a file row points to the ticket, because the database cannot delete a file in object storage.

• ON DELETE CASCADE for messages and vectors: they mean nothing without their ticket.
• ON DELETE SET NULL for AI runs: the decision from the last lesson. Priya’s monthly costs stay right.
• No action for attachments. The file itself is in object storage, outside the database. If the database deleted the row, nobody would know that the file still exists. So the database refuses, and the program must delete the file first. Recovery and retention builds that step.

Each fact in one place, and one copy on purpose

Before the rules, Mei checks one more design question. Think of an address book. You write a friend’s address once, in the book. Suppose that you also wrote it on every birthday card list. After a move, some lists would have the old address, and you would not know which one is right.

A database has the same problem. Here is a made-up flat table, of the kind that an export often has:

ticket_id	customer_id	customer_email
T-30002	C-0022	hugo.garcia@example.com
T-30111	C-0022	hugo.garcia@example.com
T-30119	C-0022	hugo.g@example.com

Hugo changed his email, and only one row got the change. Which address is right? The table cannot say. Normalization is the design rule that stops this: each fact is stored in one place, and other rows point to it by key. In the help desk, the email is only in customers, and a ticket holds only C-0022. The ERD of the last lesson is already normalized.

Sometimes a copy is worth its cost. The API gets a ticket list in Safe queries. It shows how many messages each ticket has, and when the last one came. To count them for every row of every page, the database must read the messages again and again. So migration 006 keeps a copy of both values on the ticket. This is a deliberate denormalization: a copy, made on purpose, with a written reason.

migrations/006_ticket_message_counts.sql (first part)

-- Deliberate denormalization: the ticket list shows how many messages a ticket
-- has and when the last one came. Counting them for every row of every list is
-- slow, so the ticket keeps both numbers. The cost: every new message must
-- update them in the same transaction (ticket_api/repository.py does).
ALTER TABLE tickets
    ADD COLUMN message_count integer NOT NULL DEFAULT 0,
    ADD COLUMN last_message_at timestamptz,
    ADD CONSTRAINT tickets_message_count_not_negative CHECK (message_count >= 0);

The rest of 006 fills the two columns from the messages. The comment names the cost; ticket_api/repository.py, which it mentions, arrives in Module 3. A constraint cannot check that message_count equals the real count, because a CHECK sees only one row. The program must update the copy every time it adds a message, and a test checks that they agree. Transactions shows what goes wrong when the program forgets, or when two requests come at the same time.

Guided practice: give the database its rules

1. Make the first draft of migrations/005_integrity_constraints.sql with the three lines above (the ALTER TABLE messages with its foreign key). Run it:

   python -m ticket_api.migrate

   The migration stops with ForeignKeyViolation and Key (ticket_id)=(T-29994) is not present in table "tickets". Check that nothing was applied:

   python -m ticket_api.migrate --status

   The last line is pending 005_integrity_constraints.

2. Replace your draft with the real 005, and copy 006, from the snapshot:

   cp ../nextia-learning-labs/C20/snapshots/end-of-m02/migrations/00{5,6}_*.sql migrations/

   On Windows, in PowerShell: Copy-Item ..\nextia-learning-labs\C20\snapshots\end-of-m02\migrations\005_*.sql, ..\nextia-learning-labs\C20\snapshots\end-of-m02\migrations\006_*.sql migrations\.

   Open 005_integrity_constraints.sql and read it. It has eight numbered parts, one per table, each with a comment.

3. Apply both migrations:

   python -m ticket_api.migrate

   applied  005_integrity_constraints
   applied  006_ticket_message_counts
   Database is up to date (6 migrations).

4. Open psql, and send the same mistakes as before, this time without BEGIN. Each statement now fails on its own:

   INSERT INTO customers (customer_id, name, email, segment, joined_on)
   VALUES ('C-0041', 'Olga Olsen', 'Olga.Olsen@Example.com', 'home', '2026-10-09');
   INSERT INTO messages (ticket_id, author, body) VALUES ('T-99999', 'agent', 'Hello');
   UPDATE tickets SET priority = 4 WHERE ticket_id = 'T-30002';
   UPDATE tickets SET status = 'closed' WHERE ticket_id = 'T-30002';
   INSERT INTO tickets (ticket_id, subject) VALUES ('30003', '');

   ERROR:  duplicate key value violates unique constraint "customers_email_unique"
   DETAIL:  Key (lower(email))=(olga.olsen@example.com) already exists.
   ERROR:  insert or update on table "messages" violates foreign key constraint "messages_ticket_fk"
   DETAIL:  Key (ticket_id)=(T-99999) is not present in table "tickets".
   ERROR:  new row for relation "tickets" violates check constraint "tickets_priority_range"
   DETAIL:  Failing row contains (T-30002, C-0022, Charged twice for order LK-182074, My card was charged two times for order LK-182074, 449.00 dollar..., email, billing, 4, open, 2026-09-25 08:20:19+00, 2026-09-25 19:59:19+00, null, 1, 2026-09-25 19:59:19+00).
   ERROR:  new row for relation "tickets" violates check constraint "tickets_closed_at_matches_status"
   DETAIL:  Failing row contains (T-30002, C-0022, Charged twice for order LK-182074, My card was charged two times for order LK-182074, 449.00 dollar..., email, billing, 1, closed, 2026-09-25 08:20:19+00, 2026-09-25 19:59:19+00, null, 1, 2026-09-25 19:59:19+00).
   ERROR:  null value in column "customer_id" of relation "tickets" violates not-null constraint
   DETAIL:  Failing row contains (30003, null, , null, null, null, null, open, 2026-10-10 04:27:28.864201+00, 2026-10-10 04:27:28.864201+00, null, 0, null).

   Five writes, five refusals, each with the name of its rule. The last row shows the defaults at work: status became open, and both times became now() (your times differ). The NOT NULL rule on customer_id stopped it first, so the empty subject and the format of 30003 were never checked.

5. Try to delete T-30034, the ticket with two photos:

   DELETE FROM tickets WHERE ticket_id = 'T-30034';

   ERROR:  update or delete on table "tickets" violates foreign key constraint "attachments_ticket_fk" on table "attachments"
   DETAIL:  Key (ticket_id)=(T-30034) is still referenced from table "attachments".

   The files protect their ticket. Look at the kept orphans with SELECT ticket_id, author, body FROM orphaned_messages;: three rows, T-29994, T-29997 and T-29999. Quit with \q.

6. Copy the module’s completion check, the test file tests/test_schema.py, and run the tests:

   cp ../nextia-learning-labs/C20/snapshots/end-of-m02/tests/test_schema.py tests/
   python -m pytest

   On Windows: Copy-Item ..\nextia-learning-labs\C20\snapshots\end-of-m02\tests\test_schema.py tests\. The tests print 49 passed. They use their own database, tickets_test, with the same migrations.

7. Load the data again, to see what the loader does with the orphans now:

   python -m scripts.load --reset

   The counts end with messages 331 and 3 messages have no ticket: they are in orphaned_messages.

tests/test_schema.py is the module’s completion check: the database rejects a duplicate and an orphan, with no help from the API. Its test names say what each test proves:

tests/test_schema.py (the test names)

def test_a_duplicate_email_is_rejected_whatever_its_case(conn): ...
def test_a_duplicate_customer_id_is_rejected(conn): ...
def test_an_orphan_message_is_rejected(conn): ...
def test_a_ticket_for_a_customer_who_does_not_exist_is_rejected(conn): ...
def test_impossible_values_are_rejected(conn, column, value): ...   # 5 cases
def test_a_closed_ticket_needs_a_closing_time(conn): ...
def test_the_old_orphan_messages_were_kept_apart(conn): ...
def test_deleting_a_ticket_deletes_its_messages_and_vector(conn): ...
def test_a_ticket_with_files_cannot_be_deleted(conn): ...
def test_ai_run_costs_stay_when_their_ticket_is_deleted(conn): ...
def test_message_counts_match_the_messages(conn): ...

Common mistake

The migration stops at the first old row that breaks the new rule

Symptom: python -m ticket_api.migrate stops with psycopg.errors.ForeignKeyViolation: insert or update on table "messages" violates foreign key constraint "messages_ticket_fk" and Key (ticket_id)=(T-29994) is not present in table "tickets". --status still shows the migration as pending.

Cause: a new constraint must hold for every row that exists, not only for new rows. Three old messages point to tickets that do not exist. The error names only the first one.

Diagnose: find all the rows that break the rule before you add it. For a foreign key, use the orphan query of the last lesson (LEFT JOIN … WHERE t.ticket_id IS NULL): it returns T-29994, T-29997 and T-29999. For a CHECK, select the rows where the condition is false.

Fix: decide what happens to those rows, in the same migration, before the rule. Do not delete data on a guess: 005 moves the orphans to orphaned_messages, for a person to check. Because a migration is one transaction, a failed attempt leaves nothing behind, so you can fix the file and run it again.

Independent variation: a rule of your own

Grace wants every email address to contain an @. Write the rule as a CHECK named customers_email_has_at. Test it without a change to your project: put it between BEGIN; and ROLLBACK;, with an UPDATE that breaks it. Then answer: could a CHECK also say that a message is never older than its ticket?

Show an example answer

BEGIN;
ALTER TABLE customers ADD CONSTRAINT customers_email_has_at CHECK (email LIKE '%_@_%');
UPDATE customers SET email = 'olga.olsen.example.com' WHERE customer_id = 'C-0001';
ROLLBACK;

BEGIN
ALTER TABLE
ERROR:  new row for relation "customers" violates check constraint "customers_email_has_at"
DETAIL:  Failing row contains (C-0001, Olga Olsen, olga.olsen.example.com, home, north, 2024-10-18, 2024-10-18 09:42:49+00).
ROLLBACK

'%_@_%' means: at least one character, an @, at least one character. After ROLLBACK, the rule is gone again.

The second rule cannot be a CHECK, because a CHECK sees only the row that is written. If you try it with a subquery, PostgreSQL refuses: ERROR: cannot use subquery in check constraint. A rule across two tables needs the program, a trigger or a regular check query. Here, the query finds no such message today.

When to use constraints, and what they cost

Put a rule in the database when it must hold for every writer, now and later: keys, required values, known values, and links between tables. Keep in the program the rules that depend on the situation, such as “a refund needs a supervisor above 200 dollars”.

Constraints cost something too:

• Every write is checked. A foreign key looks up the other table on each insert. For the help desk’s writes, that cost is small.
• Deletes check the children. Deleting a ticket makes PostgreSQL look for its messages, runs and files. Without an index on those ticket_id columns, each look is a scan of the whole table. Index deliberately measures it.
• Adding a rule to a big table takes time, because every old row is checked. Migrations measures 005 on 300,000 tickets.
• A rule makes old mistakes visible, like the three orphans. That is the point, but plan time to decide what happens to them.

See it end to endDesign and tune storage for an open public datasetCase study

The Boston 311 case study counts the rows that break each rule before it adds a constraint. One rule breaks for 464 real requests.

Knowledge check

Check your understanding

A colleague adds UNIQUE (email) to customers, not the unique index on lower(email). Which of today's inserts would it let through, and why?

Show the answer

It lets through the second Olga Olsen with Olga.Olsen@Example.com, because that text differs from olga.olsen@example.com in its capital letters. Email addresses are the same in any case in practice, so the help desk would have two accounts for one person. The index on lower(email) compares the lower-case forms, and refuses it with Key (lower(email))=(olga.olsen@example.com) already exists.

Check your understanding

Why does the foreign key from attachments to tickets have no ON DELETE CASCADE, when messages have one?

Show the answer

A message lives only in the database, so deleting it with its ticket leaves nothing behind. An attachment row describes a file in object storage, and the database cannot delete that file. With CASCADE, the row would go and the file would stay, and nobody would know about it any more. So the database refuses the delete, and the program must delete the files first.

Check your understanding

message_count copies information that the messages table already has. Name the reason for the copy, its risk, and how the project checks it.

Show the answer

The reason is speed: the ticket list shows the count for every row, and counting the messages again for every page is slow. The risk is that the copy and the messages disagree, if a program adds a message and does not update the count. A CHECK cannot compare with another table, so the test test_message_counts_match_the_messages checks that every ticket’s count equals its number of messages.

Recap

• A constraint is a rule that the database checks on every write, for every program. A write that breaks it is refused, nothing of it is saved, and the error names the rule.
• Migration 005 adds NOT NULL, CHECKs (formats, known values, priority 1 to 3, closing time exactly when closed), a unique index on lower(email) and foreign keys.
• A new rule must hold for old rows too. The first draft stopped at the orphan T-29994; the real 005 keeps the three orphans in orphaned_messages.
• Each foreign key says what a delete does: CASCADE for messages and vectors, and SET NULL for AI runs. Attachments refuse, because their files live outside the database.
• Normalization keeps each fact in one place. 006 makes one deliberate copy, message_count, and the program must keep it right.
• tests/test_schema.py passes: 49 passed. Next, two changes that must happen together, and two requests at the same moment.

Next: Transactions