An integrity constraint describes a database state that should be allowed. For cleaning, it is often easier to write the inverse: a query that returns every row or tuple combination that violates the rule. The correct result is an empty set.
From prose to a violation query
Rule: every resolved city must record how it was obtained.
SELECT id, canonical_city, city_source
FROM menu_clean
WHERE canonical_city IS NOT NULL
AND city_source IS NULL;Rule: every paper-author relationship must reference an existing paper.
SELECT pa.paper_id
FROM paper_author AS pa
LEFT JOIN paper AS p ON p.paper_id = pa.paper_id
WHERE p.paper_id IS NULL;These queries are useful during migration even when the final database will enforce NOT NULL or FOREIGN KEY. A constraint blocks new bad states; a violation query measures the existing ones.
In plain terms: the rule says what a clean room looks like. The violation query points at every object out of place.
Four common rule shapes
| Rule type | Example | SQL mechanism |
|---|---|---|
| Tuple | start must not exceed end | CHECK or row filter |
| Key | identifier must be unique | UNIQUE, GROUP BY |
| Referential | child must have parent | FOREIGN KEY, anti-join |
| Multi-row semantic | one location should not imply several cities | aggregation or self-join |
Some rules fit declarative database constraints; others depend on historical context, external vocabularies, or aggregate evidence and belong in validation jobs.
Missing is not automatically invalid
CHECK (year BETWEEN 1850 AND 2012)In SQL, a CHECK passes when its expression is TRUE or UNKNOWN, so a nullable year may still pass. If missingness is forbidden, state both conditions:
year INTEGER NOT NULL CHECK (year BETWEEN 1850 AND 2012)This distinction is valuable in quality reporting. A present but impossible date is a validity violation; a missing date is a coverage gap. Combining them exaggerates one metric and hides the other.
Measure before and after honestly
For every rule, report:
- population to which the rule applies;
- number of violating rows and distinct entities;
- treatment of null and unknown values;
- whether the rule existed before cleaning or is a new post-condition;
- examples and repair disposition.
Do not claim improvement from not applicable to zero. A derived provenance column did not exist in the raw schema, so its constraint can be a useful post-condition without being a before-and-after win.
Constraint order affects repair
One change can expose or resolve another violation. Canonicalizing identifiers before checking foreign keys may reconnect rows. Deduplicating parents before choosing the survivor may orphan children. A pipeline should therefore stage rules:
- parse and normalize representation;
- establish identity and canonical values;
- repair references;
- check cross-row and business constraints;
- enforce stable post-conditions.
Review card
- A violation query should return no rows on clean data.
- Use database constraints for stable local invariants and validation jobs for contextual rules.
- Missing and invalid are separate quality states.
- Report rule population, null semantics, and entity counts.
- New post-conditions are not historical improvements.
- Repair order matters because constraints interact.