PART 1 · LINK AND DOMAIN INTEGRITY
Integrity Constraints and Composite Foreign Keys
How to implement the business rules from Lesson 2 (“Only actual participants in this match are added to the kill_log”) in the table code
-- 1. Составной первичный ключ в таблице участников матча
CREATE TABLE game.match_participants (
match_id integer NOT NULL,
player_id integer NOT NULL,
team varchar(16) NOT NULL,
score integer NOT NULL DEFAULT 0,
CONSTRAINT pk_match_participants PRIMARY KEY (match_id, player_id),
CONSTRAINT chk_mp_team CHECK (team IN ('red', 'blue')),
CONSTRAINT chk_mp_score CHECK (score >= 0)
);
-- 2. Журнал фрагов ссылается СОСТАВНЫМ FK по (match_id, player_id)!
CREATE TABLE game.kill_log (
kill_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
match_id integer NOT NULL,
killer_id integer NOT NULL,
victim_id integer NOT NULL,
weapon varchar(48) NOT NULL,
CONSTRAINT chk_kill_not_self CHECK (killer_id <> victim_id),
CONSTRAINT fk_kill_killer FOREIGN KEY (match_id, killer_id)
REFERENCES game.match_participants(match_id, player_id) ON DELETE CASCADE,
CONSTRAINT fk_kill_victim FOREIGN KEY (match_id, victim_id)
REFERENCES game.match_participants(match_id, player_id) ON DELETE CASCADE
);
What are the benefits of a composite foreign key?
• If `killer_id` simply referred to `players` (`player_id`), it would be possible to log a player in the `kill_log` who never even joined that match.
• The composite foreign key (match_id, killer_id) physically prevents such an anomaly at the PostgreSQL kernel level.
Protection Against Logical Contradictions
• CHECK (killer_id <> victim_id) will prevent a fragment from being written to itself due to a bug in the network code.
• In the matches table, the CHECK constraint (finished_at IS NULL OR finished_at >= started_at) prevents a fight from having a negative duration.
Conclusion: A well-designed system architecture makes it impossible for an invalid state to persist—even if there is an error in the service's code.