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.
偵測到 violation，不代表知道哪個 value 正確。如果兩個 tuple 違反 key，刪掉任一筆都能恢復 consistency。Database-repair theory 從 validation 結束的地方開始：表示多個可能的 clean state，再問哪些結論在每個 state 都成立。
一個 violation，多個 repair
假設 email 應決定 customer_id：
| customer_id | |
|---|---|
| 17 | a@example.com |
| 42 | a@example.com |
可以刪第一筆、刪第二筆；若允許 update，也可以改值。Integrity constraint 本身無法選擇。
白話來說： 煙霧警報能證明有問題，不能告訴你是哪個物件先起火。
Repair 需要 policy
Formal repair 通常要指定：
- 必須滿足的 integrity constraint；
- 允許 delete、update、insert 或 merge 哪些 operation；
- minimality／cost measure；
- trusted source 或 priority；
- null／unknown semantics。
Set-minimal deletion 表示刪除集合不能再縮小；cardinality-minimal repair 最小化 changed tuple 數；weighted repair 可保護較可信或昂貴的 record。不同 policy 可能選出不同結果。
Consistent query answering
與其太早選一個 repair，可以先問：這個答案是否在每個 admissible repair 都成立？
certain answer = 所有 repair query answer 的 intersection
possible answer = 所有 repair query answer 的 union若兩個 repair 都表示至少一位 customer 使用 a@example.com，這是 consistent conclusion；具體是哪個 customer ID 則不是。
這在實務也有用：dashboard 可發布穩定 aggregate，同時標記依賴未解選擇的 entity assignment。
「改最少」仍不夠
最小 edit 可能語意錯誤。刪一筆很便宜，卻可能刪掉真人；改 identifier 可能違反 external ownership。實務系統還要加入：
- source authority 與 timestamp；
- confidence 與 review state；
- field-level edit cost；
- downstream impact；
- reversible change log。
證據不足時，repair 應是 proposal，不是 silent overwrite。
Repair 作為 pipeline stage
- 偵測 violation，保存 witness。
- 把相連 violation 分成 repair unit。
- 按明確 policy 產生 candidate repair。
- 對 candidate 評分或 review。
- 套用到 derived column 或新版本。
- 重跑所有 constraint 並 reconcile count。
Raw value 與 canonical value 並存，日後才能測替代 policy。
複習卡
- Violation 不會唯一指出正確 repair。
- Repair semantics 定義可用 change 與 minimality。
- 不同 policy 可能產生不同 clean database。
- Consistent answer 在所有 admissible repair 都成立。
- Minimal edit 仍需要 domain evidence。
- 保存 raw value、witness、candidate 與 selected rule。