pgz-sport/_audit/data_integrity_20260505_0836/D_CONSTRAINTS.md

Subagent D — Schema Quality Constraints (pgz_sport.clanovi)

Date: 2026-05-05 Live row count: 3240 (backup retained at pgz_sport.clanovi_backup_20260505_0836 = 3243)

Summary table

#	Candidate	Type	Pre-flight violators	Status	Object name
C1	No internal CamelCase boundary	CHECK	0	APPLIED	clanovi_no_camelcase_chk
C2	ime/prezime trimmed	CHECK	0	APPLIED	clanovi_trimmed_chk
C3	length(ime) >= 2 AND length(prezime) >= 2	CHECK	22	SKIPPED (see D_violations.md)	—
C4	spol IN ('M','Ž',NULL)	CHECK	0	ALREADY PRESENT	clanovi_spol_check (pre-existing)
C5	hns_igrac_id partial UNIQUE	UNIQUE INDEX	0 dup-groups	APPLIED	clanovi_hns_uniq
C6	(klub_id, lower(ime), lower(prezime), datum_rodenja) UNIQUE	UNIQUE INDEX	68 dup-groups	SKIPPED (see D_violations.md)	—
C7	BEFORE INSERT/UPDATE normalize trigger	TRIGGER	n/a	APPLIED	clanovi_normalize_trigger + pgz_sport.clanovi_normalize_fn()

Trigger semantics

clanovi_normalize_fn:

1. Always trim() NEW.ime and NEW.prezime.
2. On INSERT, or on UPDATE only when ime or prezime actually change:
   • reject CamelCase boundary (lenient: only ascii+Croatian-diacritic lower→upper pairs);
   • reject length(ime) < 2 or length(prezime) < 2.
3. The "only-when-name-changes" rule preserves the 22 legitimate historical short-name rows (e.g. id=1852..2141, mostly placeholder '-' ime + surname-only entries) so they can still receive UPDATEs on other fields.

Smoke insert tests (all wrapped in BEGIN/ROLLBACK so live data unchanged)

#	Scenario	Expected	Result
1	INSERT ('IvoIvic','Test')	reject (CamelCase)	REJECTED — CamelCase rejected in ime: IvoIvic
2	INSERT ('PetarPan','Test')	reject	REJECTED
3	INSERT (' Ivo ',' Ivić ')	trim then succeed	INSERTED — stored as ('Ivo','Ivić')
4	INSERT ('A','Test')	reject (length)	REJECTED — ime too short (<2 chars): A
5	INSERT ('Ivan',' X ')	trim → 'X' len 1 → reject	REJECTED — prezime too short (<2 chars): X
6	INSERT ('Marko ',' Marković')	trim then succeed	INSERTED — stored as ('Marko','Marković')
7	INSERT duplicate hns_igrac_id='209352'	reject	REJECTED — duplicate key value violates unique constraint "clanovi_hns_uniq"
8	2× NULL + 2× '' hns_igrac_id rows	all 4 succeed (partial uniqueness ignores NULL/empty)	4 INSERTS OK
9	UPDATE id=1852 (ime='-') napomena=... (no name change)	succeed	UPDATED — short-name row still mutable
10	UPDATE id=1852 ime='?' (single char)	reject	REJECTED — ime too short (<2 chars): ?

All 10 behaviours match expectations. No live row was modified — every test ROLLBACKed.

Final lockdown state on pgz_sport.clanovi

CHECK constraints in force:

• clanovi_no_camelcase_chk (NEW)
• clanovi_trimmed_chk (NEW)
• clanovi_spol_check (pre-existing)

UNIQUE indexes in force:

• clanovi_pkey (id)
• uq_clanovi_klub_profile (klub_id, profile_url) — pre-existing
• clanovi_hns_uniq (hns_igrac_id) WHERE not null/empty — NEW

User triggers in force (BEFORE INSERT OR UPDATE):

• clanovi_normalize_trigger (NEW)
• clanovi_validate_source (pre-existing)
• pgz_sport_clanovi_fts_trg (pre-existing)

Row count unchanged at 3240.