Integrity Constraints as Executable Data-Quality Rules

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:

1. parse and normalize representation;
2. establish identity and canonical values;
3. repair references;
4. check cross-row and business constraints;
5. 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.

SELECT id, canonical_city, city_source
FROM menu_clean
WHERE canonical_city IS NOT NULL
  AND city_source IS NULL;

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;

CHECK (year BETWEEN 1850 AND 2012)

year INTEGER NOT NULL CHECK (year BETWEEN 1850 AND 2012)