Detecting a violation does not tell us which value is correct. If two tuples conflict with a key, deleting either one restores consistency. Database-repair theory starts where validation stops: represent the possible clean states and ask what remains true across them.
One violation, several repairs
Suppose email should determine customer_id:
| customer_id | |
|---|---|
| 17 | a@example.com |
| 42 | a@example.com |
Possible repairs include deleting the first row, deleting the second, or changing a value if updates are allowed. The integrity constraint alone cannot choose.
In plain terms: a smoke alarm can prove there is a problem. It cannot tell you which object started the fire.
A repair needs a policy
A formal repair usually specifies:
- the integrity constraints to satisfy;
- allowed operations: delete, update, insert, or merge;
- a minimality or cost measure;
- trusted sources or priorities;
- how null and unknown values behave.
Set-minimal deletion means no proper subset of the deletions would suffice. Cardinality-minimal repair minimizes the number of changed tuples. Weighted repair can preserve trusted or expensive records. These policies can select different outcomes.
Consistent query answering
Instead of choosing one repair prematurely, ask whether an answer holds in every admissible repair.
certain answer = intersection of query answers over all repairs
possible answer = union of query answers over all repairsIf both repairs still show that at least one customer uses a@example.com, that conclusion is consistent. The specific customer ID is not.
This distinction is useful outside academic databases. A dashboard can publish stable aggregates while flagging entity assignments that depend on unresolved choices.
Why “minimal change” is not enough
The smallest edit may be semantically wrong. Deleting one of two records is cheap but may erase a real person; rewriting one identifier may violate external ownership. Practical systems add evidence:
- source authority and timestamp;
- confidence and review state;
- edit cost by field;
- downstream impact;
- reversible change logs.
Repairs should be proposals when evidence is weak, not silent overwrites.
Repair as a pipeline stage
- Detect violations and keep witnesses.
- Group connected violations into repair units.
- Generate candidate repairs under an explicit policy.
- Score or review candidates.
- Apply selected changes to derived columns or a new version.
- Re-run every constraint and reconcile counts.
Keeping raw values beside canonical values makes alternative policies testable later.
Review card
- A violation does not uniquely identify the correct repair.
- Repair semantics define allowed changes and minimality.
- Different policies can produce different clean databases.
- Consistent answers remain true in every admissible repair.
- Minimal edits still need domain evidence.
- Preserve raw values, witnesses, candidates, and the selected rule.