APPENDIX · POSTGRESQL LIVE DEMO · SLIDE P2
P2. Checking named CHECK rules in the system catalog
Click Run—let's take a look at all the CHECK restrictions that protect our game's domain
-- Проверяем все именованные ограничения CHECK в схеме game:
SELECT
rel.relname AS table_name,
con.conname AS check_name,
pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
WHERE nsp.nspname = 'game'
AND con.contype = 'c'
ORDER BY rel.relname, con.conname;
Why do we see names that make sense?
• Each rule is declared using the CONSTRAINT chk_... CHECK (...) syntax.
• Here you can see all the domain invariants of our game project:
— non-negative prices (base_price >= 0, price_paid >= 0)
— valid item rarity (common / rare / epic / legendary)
— prohibition on killing oneself (killer_id <> victim_id)
pg_constraint
Readable Errors in the API
No "magic" lines
The pg_constraint system catalog stores primary (p), foreign (f), unique (u), and check (c) constraints.
When a player attempts to perform an invalid action, the backend captures the PostgreSQL error code 23514 (check_violation) and the constraint name.
A typo in `rarity = 'legndary'` or `team = 'green'` will be corrected by the database itself before being written to disk.