Declarative constraints: why the warehouse stopped enforcing them

Why are constraints free in one database and costly in a distributed one, and why is an unenforced one still dangerous?

Primary key, unique, foreign key and check constraints are the oldest validation mechanism, and the one most clearly abandoned at scale. The reason is a cost derivation, not fashion.

Why they are cheap in a single database

In a single-node transactional database, enforcing uniqueness is almost free. The database already maintains a B-tree (a sorted index structure that supports fast lookup) on the primary key. An insert must walk that tree anyway to find where the new row belongs. Checking "does this key already exist?" is a by-product of work already done. This is a general pattern: a check that rides on a fixed cost already paid is nearly free.

Why they are expensive when the table is distributed

Spread the table over many machines and the by-product disappears. To enforce uniqueness of key K, all rows with the same K must meet on one machine. That forces one of two choices:

• Co-partition by K (store all rows with the same K together). This hands the physical layout of the table to the constraint instead of to the query patterns. You can no longer choose the layout that makes your queries fast.
• An exchange (shuffle) per write. Each insert must go and look for its key somewhere else.

Foreign keys are worse. The reference is in a different table, probably partitioned differently, so every row needs a remote lookup.

So analytical systems use the informational constraint: the constraint is declared in the table definition but not enforced when data is written.

The informational constraint is not inert

Here is the part worth pausing on. The optimizer reads declared constraints. A declared primary key tells the planner that a join against that key cannot make the result bigger (it cannot multiply rows). That fact licenses optimizations such as join elimination (leaving out a join whose result can be computed without it) and removing a DISTINCT (why de-duplicate a column that is already unique?). It also changes the row-count estimates used for the rest of the plan.

If the declaration is false, you do not get a slow query. You get a wrong answer, silently, from a correct engine.

Example. customers declares customer_id unique, but ID 7 appears twice because two loads overlapped. A query SELECT COUNT(*) FROM (SELECT DISTINCT customer_id FROM customers) may be rewritten by the optimizer to skip the DISTINCT, since the key is "known" to be unique. The count is then one too high, with no error message. That is the 4.6 million versus 4.2 million in Ravi's problem, at larger scale.

This is the purest case of the question: what does the system assume that it cannot verify? The assumption is written in the table definition. The verification is explicitly disabled. The party who pays is a query author three teams away who never read the table definition. It is also an example of late binding of an assertion: it is fixed when you declare it and checked never, so the failure appears arbitrarily far from its cause.

The corrected folklore. "Constraints are for data integrity" is only half true. In distributed analytics they are mainly optimizer hints that happen to be phrased as integrity claims. Treating them as documentation is how people get burned.

What people build instead: the inferred member

Having abandoned enforcement, teams build the inferred-member pattern from dimensional modelling (the "fact and dimension" table design used in warehouses). When a fact row (an order) has no matching dimension row (a customer), the pipeline does not reject it. It gives the order a synthetic dimension key such as "Unknown customer". Notice what this is: a foreign-key violation converted into a valid-but-meaningless value. The defect is preserved, named, and made countable. That is usually the right trade, but it is a trade. The cost lands on whoever aggregates by that dimension ("revenue per customer segment" now has an "Unknown" segment).

Compression: enforcement cost tracks write volume, benefit tracks defect rate; so the constraint survives as a claim the optimizer believes and nobody checks.