PART 1 · THE LIFE CYCLE OF RELATED RECORDS
Delete Policy: ON DELETE RESTRICT, SET NULL, and CASCADE
Mindlessly setting "ON DELETE CASCADE" on all foreign keys is a surefire way to lose a project's financial history
ON DELETE RESTRICT
ON DELETE SET NULL
ON DELETE CASCADE
You MUST NOT delete the parent
When Communication Is Not Required
A child line is part of a parent
Example in the diagram:
purchases → players and purchases → items
Why:
• Purchases are a financial and audit log.
• If an item is removed from the storefront or an account is blocked, the transaction history must remain.
• The DBMS will block a `DELETE FROM game.items` if someone has already purchased the item.
Example in the diagram:
players.clan_id → clans(clan_id)
Why:
• A player may belong to a clan or may play alone (clan_id IS NULL).
• When a clan is dissolved (DELETE FROM game.clans), the players themselves cannot be deleted!
• The DBMS will automatically set `clan_id = NULL` for former members.
Example in the diagram:
match_participants → matches and kill_log → match_participants
Why:
• Match participation and the in-match kill log make no sense without the match itself.
• When clearing a test match, the DBMS will automatically delete its roster and kill log.
DDL review rule: Every foreign key must have an explicit ON DELETE clause. By default, PostgreSQL sets it to NO ACTION / RESTRICT.