Knobit of Data modelling · K-625 · Skill

Recognises a soft delete the constraints do not understand

What it means

Rows marked deleted but still present, with keys and foreign keys unaware of the mark. The deleted row blocks a valid insert, or uniqueness is dropped and active duplicates load, and active rows reference deleted ones.

How it is assessed

Flags the deficient case and passes the acceptable one. Deficient: `party.is_deleted`; the unique constraint on `registry_code` includes deleted rows, so the company cannot be registered again; payments keep pointing at a deleted party Acceptable: Uniqueness over active rows only (a filtered unique index where the dialect has one, otherwise a DQ rule); a rule on what may reference a deleted row; views that expose active rows; or deleted rows moved to a history table

Builds on

Explains referential integrity and referential actionsK-653 · Knowledge

Needed by

None