Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.5.9, 10.5
-
None
-
Linux 5.11.11
mariadb:10.4 or mariadb:10.5 from Docker Hub
Description
Trying to reference a composite foreign key in a CHECK clause fails:
ERROR 1901 (HY000): Function or expression 'SomeColumn' cannot be used in the CHECK clause of `CONSTRAINT_1`
For example, running MariaDB 10.5 in a container (docker run -p 127.0.0.1:3305:3306 --name test -e MYSQL_ROOT_PASSWORD=root -d mariadb:10.5) and executing this:
CREATE DATABASE `Example`;
|
USE `Example`;
|
|
CREATE TABLE `Foo`
|
(
|
`FooID` VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL,
|
`SomeColumn` VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL,
|
PRIMARY KEY (`FooID`, `SomeColumn`)
|
)
|
ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4
|
COLLATE = utf8mb4_unicode_ci;}}
|
|
CREATE TABLE `Bar`
|
(
|
`BarID` VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL,
|
`FooID` VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL,
|
`SomeColumn` VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
`AnotherColumn` VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
PRIMARY KEY (`BarID`),
|
CONSTRAINT FOREIGN KEY (`FooID`, `SomeColumn`) REFERENCES `Foo` (`FooID`, `SomeColumn`)
|
ON DELETE NO ACTION
|
ON UPDATE CASCADE,
|
CHECK ((`SomeColumn` IS NOT NULL AND `AnotherColumn` IS NULL)
|
OR (`SomeColumn` IS NULL AND `AnotherColumn` IS NOT NULL))
|
)
|
ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4
|
COLLATE = utf8mb4_unicode_ci;
|
The CREATE TABLE `Bar` query fails with the error:
ERROR 1901 (HY000): Function or expression 'SomeColumn' cannot be used in the CHECK clause of `CONSTRAINT_1`
However, this worked on MariaDB 10.4. Starting another container with mariadb:10.4 and executing the same queries succeeds, both tables are created and all the constraints work as expected:
MariaDB [Example]> INSERT INTO Foo VALUES ("a", "b");
|
Query OK, 1 row affected (0.014 sec)
|
|
MariaDB [Example]> INSERT INTO Bar VALUES ("checkme", "a", "b", "c");
|
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `Example`.`Bar`
|
|
MariaDB [Example]> INSERT INTO Bar VALUES ("checkme2", "a", "b", NULL);
|
Query OK, 1 row affected (0.006 sec)
|
|
MariaDB [Example]> INSERT INTO Bar VALUES ("checkme3", "a", NULL, "c");
|
Query OK, 1 row affected (0.006 sec)
|
|
MariaDB [Example]> INSERT INTO Bar VALUES ("checkme4", "doesnotexist", NULL, "c");
|
Query OK, 1 row affected (0.012 sec)
|
|
MariaDB [Example]> INSERT INTO Bar VALUES ("checkme5", "doesnotexist", "willfail", NULL);
|
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`Example`.`Bar`, CONSTRAINT `Bar_ibfk_1` FOREIGN KEY (`FooID`, `SomeColumn`) REFERENCES `Foo` (`FooID`, `SomeColumn`) ON DELETE NO ACTION ON UPDATE CASCADE)
|