Foreign Keys and Referential Integrity

Link tables with REFERENCES — make orphaned rows impossible

A beer rating refers to a beer that no longer exists. A tap assignment points to a brewery that was deleted. These are orphaned rows — they pass every check constraint but they reference something that is not there.\n\nForeign keys close this gap. They tell the database: this column must always contain a value that exists in that other table. And when the parent disappears, the FK decides: cascade (delete the children too), restrict (block the deletion), or set null (disconnect the children gracefully).

The Orphaned Row Problem

Without foreign keys, any column can contain any value. If beer_id in beer_ratings is just a uuid with no FK, nothing stops:

Inserting a rating for a beer_id that doesn't exist
Deleting a beer while its ratings remain
An import script creating 10,000 ratings with wrong beer IDs

Foreign keys prevent all three scenarios at the engine level.

The beer_db FK Chain

The existing schema has a cascade chain:

breweries → beers (ON DELETE CASCADE)
         → beer_ratings (ON DELETE CASCADE)

This means deleting a brewery cascades to delete all its beers, which cascades to delete all those beers' ratings. One DELETE, consistent cleanup.

Choosing the Right Action

ON DELETE CASCADE     → child rows are deleted automatically
                        use when: ratings, line items, attachments
ON DELETE RESTRICT    → deletion is blocked if children exist
                        use when: you want to force explicit cleanup
ON DELETE SET NULL    → FK column is set to NULL, child row stays
                        use when: optional relationship (beer → style)
ON DELETE NO ACTION   → same as RESTRICT but checked at statement end
                        (default if not specified)

Foreign key (REFERENCES)

A constraint that says: every value in this column must exist as a primary key (or unique key) in the referenced table. Enforced on every INSERT and UPDATE to the child table, and on every DELETE from the parent table.

DEFERRABLE INITIALLY DEFERRED

By default, FK checks happen at statement execution time. DEFERRABLE INITIALLY DEFERRED moves the check to the end of the transaction. Useful when two tables reference each other (circular FKs) — you can insert both rows in one transaction and the check runs at COMMIT.

🔌 Connect to the Beer Database

Before exploring foreign keys, connect to beer_db. Run psql -U beer_db -d beer_db to open a psql session.

psql -U beer_db -d beer_db

beer_db=>

🔍 Examine the FK Chain in beer_db

Run \d beers and look at the "Foreign-key constraints" and "Referenced by" sections. Trace the cascade chain: breweries → beers → beer_ratings.

\d beers

Foreign-key constraints: "beers_brewery_id_fkey" FOREIGN KEY (brewery_id) REFERENCES breweries(id) ON DELETE CASCADE Referenced by: TABLE "beer_ratings" CONSTRAINT "beer_ratings_beer_id_fkey" FOREIGN KEY (beer_id) REFERENCES beers(id) ON DELETE CASCADE

💥 Try to Insert a Rating for a Non-Existent Beer

Attempt to insert a beer_rating with a fake beer_id. The FK constraint will reject it because the referenced beer does not exist.

INSERT INTO beer_ratings (beer_id, reviewer_name, score) VALUES ('00000000-0000-0000-0000-000000000000', 'Ghost Reviewer', 7.0);

ERROR: insert or update on table "beer_ratings" violates foreign key constraint "beer_ratings_beer_id_fkey" DETAIL: Key (beer_id)=(00000000-0000-0000-0000-000000000000) is not present in table "beers".

🌊 Observe Cascade Delete in Action

Insert a test brewery, insert a beer for it, insert a rating for that beer. Then delete the brewery. Verify all three rows are gone.

INSERT INTO breweries (name, city) VALUES ('Cascade Test Brewery', 'Olomouc') RETURNING id;

INSERT INTO beers (brewery_id, name, style, abv) VALUES ((SELECT id FROM breweries WHERE name = 'Cascade Test Brewery'), 'Cascade Pale Ale', 'Pale Ale', 4.8) RETURNING id;

INSERT INTO beer_ratings (beer_id, reviewer_name, score) VALUES ((SELECT id FROM beers WHERE name = 'Cascade Pale Ale'), 'Cascade Tester', 8.0) RETURNING id;

DELETE FROM breweries WHERE name = 'Cascade Test Brewery';

SELECT COUNT(*) FROM beers WHERE name = 'Cascade Pale Ale';

SELECT COUNT(*) FROM beer_ratings WHERE reviewer_name = 'Cascade Tester';

DELETE 1 count ------- 0 count ------- 0

🔒 See RESTRICT Prevent an Unsafe Delete

The festival_appearances and tap_assignments tables both have FKs to beers WITHOUT cascade. Find a beer that has festival appearances and try to delete it — the FK will block you.

SELECT beer_id FROM festival_appearances LIMIT 1;

DELETE FROM beers WHERE id = (SELECT beer_id FROM festival_appearances LIMIT 1);

ERROR: update or delete on table "beers" violates foreign key constraint "tap_assignments_beer_id_fkey" on table "tap_assignments" DETAIL: Key (id)=(...) is still referenced from table "tap_assignments".

Lab complete! Referential integrity is enforced at the engine level:\n\n\n FK insert check : orphaned ratings blocked ✅\n ON DELETE CASCADE: beers and ratings cleaned up ✅\n ON DELETE RESTRICT: festival appearances protected ✅\n

Enable JavaScript to run the live terminal and track your progress.