Error 1901 incorrectly raised when adding CHECK constraint on a FK column whose referenced table also has FKs

Component: SQL Layer / Constraints

Affects version: 11.8.6-MariaDB (also reproduced on 10.11.16-MariaDB)

Severity: Major

Description

ALTER TABLE ... ADD CONSTRAINT CHECK fails with error 1901 (Function or expression 'X' cannot be used in the CHECK clause) when:

The child table has a nullable column col with a FOREIGN KEY referencing table P, and
The referenced table P itself has at least one outbound FOREIGN KEY to another table.
The CHECK expression does not reference any function — only a plain column reference with IS NULL. The same statement succeeds when the referenced table P has no outbound FKs, proving the error is spurious.

Minimal reproducible example

– Three-level chain: grandparent ← parent ← child

CREATE TABLE grandparent (

  id VARCHAR(64) NOT NULL PRIMARY KEY

) ENGINE=InnoDB;

– parent has an outbound FK to grandparent

CREATE TABLE parent (

  id     VARCHAR(64) NOT NULL PRIMARY KEY,

  gp_id  VARCHAR(64) NOT NULL,

  INDEX (gp_id),

  CONSTRAINT FOREIGN KEY (gp_id) REFERENCES grandparent(id) ON UPDATE CASCADE

) ENGINE=InnoDB;

– child has a nullable FK to parent

CREATE TABLE child (

  id        INT AUTO_INCREMENT PRIMARY KEY,

  parent_id VARCHAR(64) NULL,

  custom_id VARCHAR(64) NULL,

  INDEX (parent_id),

  CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB;

– This fails with ERROR 1901:

ALTER TABLE child

  ADD CONSTRAINT xor_check CHECK ((parent_id IS NULL) != (custom_id IS NULL));

Error:

ERROR 1901 (HY000): Function or expression 'parent_id' cannot be used in the CHECK clause of `xor_check`
Expected behavior

The ALTER TABLE should succeed. The CHECK expression (parent_id IS NULL) != (custom_id IS NULL) references only plain column names with the IS NULL operator — no functions, no generated columns, no expressions disallowed in CHECK clauses.

Observed behavior

Error 1901 is raised. The same ALTER succeeds if parent has no outbound FKs (i.e., if you drop the grandparent table and the FK from parent). This confirms the error is triggered by the FK chain depth, not by the CHECK expression itself.

Additional observations

The CHECK constraint can be defined inline in the CREATE TABLE statement (even with the FK present) without error — the bug only surfaces with ALTER TABLE ... ADD CONSTRAINT CHECK after the FK already exists.
The bug is reproducible both in a single-statement mariadb --execute call and interactively.
Tested on: 11.8.6-MariaDB-ubu2404 and 10.11.16-MariaDB.
A Prisma migration (prisma migrate deploy) hitting this code path receives database error code 1901 and aborts, making the schema undeployable.
Workaround

Define the CHECK constraint inline in the original CREATE TABLE statement before any FKs on the referenced chain exist. ALTER TABLE ADD CONSTRAINT CHECK after the fact is not viable when the referenced table has its own FKs.