[MDEV-25437] Cannot reference composite foreign key in CHECK clause Created: 2021-04-17  Updated: 2023-08-17  Resolved: 2021-04-19

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.5.9, 10.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Ludovico Pavesi Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

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)



 Comments   
Comment by Alice Sherepa [ 2021-04-19 ]

Thanks for the report! I repeated as described.
Error on 10.5 after 762bf7a03b revision:

create table t1 ( a int not null primary key);
 
create table t2 ( a int, check (a is not null),
 
	constraint foreign key (a) references t1 (a) on update cascade);
 
drop table t2,t1;

10.5

 
MariaDB [test]> create table t1 ( a int not null primary key) engine = innodb;
 
Query OK, 0 rows affected (0.051 sec)
 
 
 
MariaDB [test]> create table t2 ( a int, check (a is not null),
 
    -> constraint foreign key (a) references t1 (a) on update cascade) engine = innodb;
 
ERROR 1901 (HY000): Function or expression 'a' cannot be used in the CHECK clause of `CONSTRAINT_1`

Comment by Sergei Golubchik [ 2021-04-19 ]

This is intentional. The way MariaDB works right now (and always did) cascading updates to child tables happen on a very low level where there is no possibility of checking a CHECK constraint. That is, if you have cascading updates, they can break CHECK constraints.

This is something we plan to fix — but it's a huge project. We're already working on it for a year, and it might take a few more.

So for now we're not allowing CHECK constraints (and related features) when cascading updates are used. This was done in MDEV-22602

Comment by Adrien Crivelli [ 2023-08-17 ]

This is something we plan to fix

@Sergei Golubchik, is there an issue for that work , so that we can follow its progress ?

Comment by Sergei Golubchik [ 2023-08-17 ]

Yes, there are two approaches to fix it. By "huge project" I meant MDEV-22361. But don't hold your breath, it'll take years.
There's a much simpler way of fixing it and we'll likely do that first. It's MDEV-31942

Generated at Thu Feb 08 09:37:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.