Some engines historically parsed CHECK constraint syntax but never enforced it — MySQL accepted and ignored CHECK clauses before version 8.0.16. What are the practical consequences of an unenforced constraint, and how would you verify that a constraint in a given database is actually enforced?

## The failure mode A constraint is a promise stored in the schema. Every reader — developers, ORMs, reporting tools, the optimizer — is entitled to rely on it. An unenforced constraint keeps the promise visible while removing the guarantee, which is strictly worse than never declaring it: with no constraint at all, a careful developer writes defensive code; with a fake one, they confidently do not. ## Where unenforced constraints come from **Historical engine behaviour.** MySQL parsed CHECK clauses in CREATE TABLE and silently ignored them for many years; enforcement arrived in **MySQL 8.0.16**. A schema authored against an older version could carry perfectly reasonable CHECK clauses that never did anything, and the same DDL behaves differently after an upgrade or when the schema is ported to another engine. **Not-validated state.** Several engines let you add a constraint that applies to future writes but was never checked against existing rows. That is a deliberate, useful operational tool, but the constraint's guarantee is then narrower than it appears: new data is clean, old data is unknown. **Disabled or untrusted constraints.** Constraints can be explicitly disabled for a bulk load and then not re-enabled, or re-enabled without validation so the engine enforces but does not trust them. An untrusted constraint still stops bad writes but is not used for query rewriting, which quietly costs performance. **Replication and copies.** A replica or a schema recreated by a dump/restore or a migration tool may or may not carry the constraint; tooling that filters DDL is a common source of drift between environments. ## Consequences in production *Silent corruption.* Rows violating the stated rule enter the table with no error anywhere. Nothing alerts, because from the writer's perspective everything succeeded. *Broken assumptions downstream.* Application code, reports and derived pipelines are written against the schema's claim. A NULL or a negative value that 'cannot happen' produces a crash, a wrong total or a division by zero far from the write that caused it. *Deferred, amplified migration pain.* The day the engine is upgraded, or someone validates the constraint, the operation fails against however many violating rows accumulated. Now you are fixing years of data under time pressure during an upgrade window — the worst possible moment. *Optimizer effects.* Where a planner uses constraints to prune partitions or eliminate predicates, an untrusted or absent constraint just means slower plans; but a constraint the planner trusts while the data violates it can yield wrong results. That is the argument for validating rather than merely asserting. ## How to verify enforcement **Test it, do not read it.** The only reliable check is behavioural. In a transaction, attempt a write the constraint must reject, observe the outcome, and roll back. Success means the constraint is not enforcing. This takes seconds and is immune to engine version, catalog quirks and DDL that lies. **Then read the catalog.** Every mainstream engine exposes constraint metadata, including whether a constraint exists at all on this object and whether it is in a validated / enabled / trusted state. Use this to distinguish 'missing' from 'present but not validated' — the remediation differs. **Audit the data.** Run the constraint's own predicate as a query with the polarity inverted to count existing violations. Remember the three-valued rule when writing that query: rows where the predicate is UNKNOWN are *not* violations, so a naive `WHERE NOT (predicate)` will miss nothing but a naive `WHERE predicate = false` phrasing may confuse readers. Count them, look at them, and decide whether to repair, quarantine or narrow the rule before enabling enforcement. **Automate it.** Put the negative test in the test suite that runs against a real database of the deployed engine version: an insert that must raise, asserted to raise. This catches the whole family of regressions — constraint dropped by a migration, environment recreated without it, engine or storage-engine change, replica drift — and it is the practice that distinguishes a senior answer. ## Remediation sequence 1. Confirm empirically what is and is not enforced, in every environment, not just the one you can see. 2. Keep the application-side validation in place until the database is proven to enforce the rule; do not remove belt because you believe braces exist. 3. Audit and fix or quarantine existing violations, in batches if the table is large. 4. Enable and validate the constraint. 5. Add the negative test so it stays enforced. ## The general lesson 'The DDL says so' is not evidence. Constraints are a control, and controls that are never exercised decay. The habit worth carrying out of this topic is that any invariant you rely on should have a test that proves the database still rejects its violation — the same reasoning you would apply to a backup you have never restored.