APPENDIX · POSTGRESQL LIVE DEMO · SLIDE P1
P1. Review of the physical schema and ON DELETE policies
Click Run—let's check the tables created in the "game" schema, the foreign keys, and their ON DELETE policies
-- Сводка внешних ключей (FOREIGN KEY) и политик ON DELETE в схеме game:
SELECT
tc.table_name AS child_table,
tc.constraint_name,
ccu.table_name AS parent_table,
rc.delete_rule AS on_delete_policy
FROM information_schema.table_constraints tc
JOIN information_schema.referential_constraints rc
ON tc.constraint_name = rc.constraint_name
AND tc.constraint_schema = rc.constraint_schema
JOIN (SELECT DISTINCT constraint_schema, constraint_name, table_name
FROM information_schema.constraint_column_usage) ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_schema = 'game' AND tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name, tc.constraint_name;
What does this query show?
• The DBeaver graphical client builds the reverse ER diagram specifically from the `information_schema` and `pg_catalog` tables.
• Note the `on_delete_policy` column:
— purchases → items / players have RESTRICT
— players → clans has SET NULL
— `kill_log` and `match_participants` have `CASCADE`
All 7 tables are in place
Self-Assessment in Practice 5
The "alternative" command in psql
The "game" schema contains the following tables: "clans," "players," "items," "purchases," "matches," "match_participants," and "kill_log."
Run the same query on your database—if it returns no results, it means that FOREIGN KEYs are missing from your DDL.
In an interactive psql session, the command \d+ game.purchases instantly displays the same relationships for a specific table.