3

Given table A:

CREATE TABLE `A` (
 `id` int NOT NULL,
 PRIMARY KEY (`id`)
);

when I try to create this second table B:

CREATE TABLE `B` (
 `id` int NOT NULL,
 `val1` int,
 PRIMARY KEY (`id`),
 CONSTRAINT `a_nonsense_foreign_key` FOREIGN KEY (`val1`) REFERENCES `A` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `a_nonsense_check` CHECK (`val1` > 10)
);

I get the following error message:

SQL Error [1901] [HY000]: (conn=6) Function or expression 'val1' cannot be used in the CHECK clause of `a_nonsense_check`

If i change the ON UPDATE CASCADE to ON UPDATE RESTRICT I get no errors.

Another observation is that ON DELETE CASCADE doesn't generate errors.

Why is it that the CHECK constraint cannot be combined with the ON UPDATE CASCADE statement? Can I find this behaviour in the documentation somewhere?

I am using MariaDB and I believe the standard engine is InnoDB.

And a short disclaimer: I am new to databases, and the root of this problem might be an XY-problem. I am, however, curious to why this limitation exists or if I am doing something wrong :)

6
  • What version are you using? 10.6 seems to work: dbfiddle.uk/Nxq_N1Zl Dec 17, 2023 at 22:02
  • Fwiw, all versions available at db<>fiddle seems to work Dec 17, 2023 at 22:14
  • fiddle exercising constraints.
    – danblack
    Dec 17, 2023 at 22:27
  • I am running MariaDB 11.2.2 on windows. So the version differs. Otherwise, could there be a setting with the database itself? Here is the reported create statement: CREATE DATABASE testdatabase /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */
    – Jonatan G
    Dec 18, 2023 at 5:58
  • I did some more testing regarding versions on Windows. The fiddle statement works in 10.9.4 but not in 10.9.5. Another observation is that fiddle breaks if you try to update the referred row in table A. Since I only get the "run failed" message from fiddle, I don't get a link to share..
    – Jonatan G
    Dec 18, 2023 at 7:25

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.