Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25437

Cannot reference composite foreign key in CHECK clause

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.5.9, 10.5
    • N/A
    • 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)
      

      Attachments

        Activity

          People

            serg Sergei Golubchik
            lvps Ludovico Pavesi
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.