APPENDIX · POSTGRESQL LIVE DEMO · SLIDE 4
P4. Testing ON DELETE RESTRICT and SET NULL in a live environment
Click "Run"—let's try to delete the purchased item and disband the clan that these players belong to
-- 1. Пробуем удалить предмет item_id = 1 ('Plasma Rifle'), который уже покупали:
DO $$
BEGIN
DELETE FROM game.items WHERE item_id = 1;
EXCEPTION WHEN foreign_key_violation THEN
RAISE NOTICE '[RESTRICT OK] Купленный предмет защищён: %', SQLERRM;
END $$;
-- 2. В транзакции с откатом (ROLLBACK) распускаем клан [SHADOW]:
BEGIN;
DELETE FROM game.clans WHERE clan_tag = 'SHADOW';
SELECT player_id, nickname, clan_id FROM game.players ORDER BY player_id;
ROLLBACK;
Analysis of the Results
• 1. ON DELETE RESTRICT preserved the purchase history: an attempt to delete item_id = 1 was blocked by the foreign key fk_purchases_item.
• 2. ON DELETE SET NULL reset the reference: after deleting the SHADOW clan, the players ShadowFox and Valkyrie remained in the table, and their clan_id became NULL!
• A ROLLBACK is included at the end so you can click Run as many times as you like.
Experiments in a Transaction
Soft Delete in Real-World Games
Cascade at Games
By wrapping the test in a BEGIN ... ROLLBACK block, you can test destructive operations on live data and cleanly roll back the changes.
Instead of physically removing an item from the `items` table, a `is_archived` boolean flag (DEFAULT false) is usually added to the table.
However, deleting a draft match (DELETE FROM game.matches) will automatically clear its participants and frags thanks to ON DELETE CASCADE.