Skip to main content

DEPLOY.LEGACY.CONSTRAINT_NOT_ENFORCED — A CHECK constraint that is not one

  • Category: safety
  • Severity: medium — a guarantee that is not kept is worth acting on, and it is not a stopped deploy. What raises it as it ages is the debt reconciliation, deliberately not this reading
  • Level: 0
  • Downtime class: online — reporting it takes no lock
  • Stability: stable
  • Suites: deploy
  • Command: sqlens:postdeploy only
  • Applies to: MySQL 8.4 and newer
  • Not transferable to MariaDB: this page describes MySQL 8.4 semantics

What it reads​

information_schema.TABLE_CONSTRAINTS joined to information_schema.CHECK_CONSTRAINTS, for CONSTRAINT_TYPE = 'CHECK' and ENFORCED = 'NO'.

Why it is worse than a constraint that is simply absent​

An absent constraint is visibly absent. ENFORCED = NO is a constraint that appears in SHOW CREATE TABLE, appears in the migration that added it, appears in code review — and admits every row it claims to refuse. Somebody looking for the guarantee finds it, and it is not there.

The view that holds the expression does not hold the flag​

Measured on MySQL 8.4.10:

SELECT CONSTRAINT_NAME, CHECK_CLAUSE FROM information_schema.CHECK_CONSTRAINTS;
  qty_positive   (`qty` > 0)
  qty_lax        (`qty` > 0)      <- NOT ENFORCED, and this view cannot tell you

SELECT TABLE_NAME, CONSTRAINT_NAME, ENFORCED FROM information_schema.TABLE_CONSTRAINTS;
  t   qty_positive   YES
  u   qty_lax        NO

That is why this check joins the two rather than reading the obvious one — and it is why a person checking by hand so often concludes the constraint is fine.

It FAILS rather than reporting undetermined, unlike its neighbors​

The two other post-deploy leftover checks report undetermined, because a NAME is not evidence and because a tool run in flight looks like one that died. Neither applies here: ENFORCED = NO is a fact the catalog states outright, there is no second reading needed, and there is no transient state that looks the same. Reporting a decided fact as undetermined would spend the three-valued contract on something that is decided.

The remedy is not a formality​

ALTER TABLE orders ALTER CHECK qty_positive ENFORCED;

That statement re-validates against the rows already in the table, and on a table that has been running unenforced it can simply fail. The likely reason the constraint was switched off is that the data did not satisfy it — which makes turning it back on a data decision rather than a schema one.

SQLens reports it as a proposal and never runs it.

What this check deliberately does NOT do​

No ledger is read or written, no age is computed, no severity is escalated. Whether an unenforced constraint is debt — how old, how bad, whether it fails a run — belongs to the debt reconciliation. Building a second opinion here would give one database two answers depending on which part asked.

What the finding does carry is the full object identity — schema, table, constraint, expression — so that reconciliation can resolve it without a second query.

Sources​