Enforce "one parent per child row" without deferred constraint triggers

A child row (e.g. an order line) must have exactly one parent, enforced by a DEFERRABLE INITIALLY DEFERRED constraint trigger checked at commit. Any plain DELETE of a parent (cleanup scripts, test teardown, purge jobs) cascades the link rows away, leaving a live child with zero parents, so the whole transaction fails at commit. Batched imports that wrap each row in its own exception block also lose: the deferred check runs at commit, outside the per-row block, so one bad row aborts the whole batch instead of becoming a row error.

Split the rule. (1) Check "at most one parent" immediately in a BEFORE INSERT trigger on the link table, with a clear message. (2) Create the child and its link in the same write path (app code, import row), so "at least one" holds by construction and a missing parent is refused inside the row's own exception block. (3) Add a BEFORE DELETE trigger on the parent table that deletes its children while the link rows still exist (in an AFTER trigger, FK cascades have already removed them). (4) Let a periodic purge remove any orphan that slips through. Cleanup scripts that fingerprint unrelated rows must treat children of deleted parents as expected changes.

Deferred constraint triggers run at commit, after FK ON DELETE CASCADE actions, and outside any savepoint or exception block opened per row.

When designing parent/child integrity rules in Postgres for rows that are also deleted by scripts, tests or batched imports.