Why is an integrity constraint described as a property of the schema rather than of the data, and what follows from saying that only legal instances are permitted - including for the intermediate states a transaction passes through?

## A constraint is a predicate, and it lives on the schema Formally, a constraint is a condition that any instance must satisfy. Declaring it on the schema divides the space of all conceivable instances into two: **legal** instances, which conform, and illegal ones, which the database is forbidden to occupy. The schema is therefore not just a shape - heading and domains - but also a filter on which populations of that shape are admissible. This is why the location of a rule is not a stylistic detail. A rule enforced by the schema holds for every instance, for every writer, forever, with no way around it. A rule that is merely true of the current instance is a coincidence with an expiry date. ## 'True today' versus 'guaranteed' The practical bite of the distinction shows up constantly: - Someone checks that an attribute has no duplicate values and concludes it is unique. It is unique *in this extension*. The next insert may end that, and nothing in the system will object. - Code assumes an attribute is never absent because it never has been. Unless absence is forbidden by the schema, that assumption is one bad write away from a defect. - A consumer assumes every referencing value has a matching referenced tuple because the application always writes both. Without a declared referential constraint, one buggy path, one bulk load, or one manual fix breaks it. There is also a reasoning benefit that application-side checking cannot provide. Because declared constraints hold for all legal instances, the query optimiser is allowed to use them: knowing an attribute is unique or never absent lets it eliminate duplicate-removal work, simplify predicates, or choose a cheaper join strategy. A rule enforced only in application code is invisible to that reasoning and buys nothing beyond the check itself. ## Enforcement is about transitions Since the database must never be observable in an illegal state, the engine enforces constraints on **state transitions**: given a legal current state and a proposed change, the resulting state must also be legal, otherwise the change is rejected. The unit of the transition is the transaction, not the individual statement - and that is the resolution of the puzzle this question is really testing. Inside a transaction, intermediate states are not states the outside world may observe, so requiring each of them to be legal is stricter than the model demands. Two classic cases make this concrete: - **Swapping unique values.** Two tuples must exchange values of a uniqueness-constrained attribute. After the first update, two tuples momentarily share a value. Checked immediately, the transaction fails; checked at commit, the final state is perfectly legal. - **Mutually referencing tuples.** Two relations each reference the other's key. Whichever tuple is inserted first has a dangling reference until the second insert lands. Only end-of-transaction checking allows the pair to be created without contortions. Hence the general design: constraints may be checked immediately, after each statement, or deferred to the end of the transaction. Deferral is not a weakening of the guarantee - no illegal state is ever committed or made visible to other transactions - it simply matches the check to the granularity at which legality is actually required. The cost is that a violation surfaces at commit time, further from the statement that caused it, which makes error attribution harder; so deferral is used where it is needed rather than as a default. It is also worth separating the constraint from any repair action attached to it. Cascading behaviour on a referential constraint does not change what is legal; it changes what the engine does to keep the state legal, propagating a deletion or update instead of rejecting it. ## Consequences when the schema changes If a constraint asserts something about every legal instance, then adding a constraint makes a claim about the instance that already exists. The engine must therefore validate the current data before the claim can be trusted - a scan whose cost scales with the data - and if today's instance violates the new rule, the choice is to fix the data first or not to add the constraint. Platforms commonly offer a two-step path: begin enforcing the rule for new changes, then validate historical data separately to limit disruption. Either way the conceptual point stands: you cannot declare a property of all instances while an instance violating it is sitting there. ## How to present it Lead with the definition - a constraint is a predicate on instances, declared on the schema, so it holds for every legal instance. Follow with the contrast between 'true of today's data' and 'guaranteed', including the optimiser-reasoning benefit. Then handle intermediate states: legality is required of committed states, which is exactly why deferred checking exists. Close with the migration consequence of validating an existing instance.