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

Multiple Foreign Keys to same column with unique keys cause foreign key constraints to fail

    XMLWordPrintable

    Details

      Description

      If you have two columns in a child table which have a foreign key constraint to the same column of the parent table the constraints will fail on update of the foreign key in the parent table if you add a multi column unique key over both columns in the child table.

      This only happens if you add unique constraints.

      SQL Code for reproduction:

      Schema

      CREATE TABLE IF NOT EXISTS user (
      id INT NOT NULL AUTO_INCREMENT,
      name VARCHAR(55),
      PRIMARY KEY (`id`)
      );
       
      CREATE TABLE IF NOT EXISTS user_map (
      map_id INT NOT NULL AUTO_INCREMENT,
      user_a INT,
      user_b INT,
        PRIMARY KEY (`map_id`),
        UNIQUE KEY `one_way` (`user_a`,`user_b`),
        UNIQUE KEY `other_way` (`user_b`,`user_a`),
        CONSTRAINT `acc_connections_ibfk_1` FOREIGN KEY (`user_a`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
        CONSTRAINT `acc_connections_ibfk_2` FOREIGN KEY (`user_b`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
      );
      

      Data

      INSERT INTO user (name) VALUES ("User A");
      INSERT INTO user_map (user_a,user_b) VALUES (1,1);
      

      Error

      UPDATE user set id = 10 where id = 1;
      

      See DB fiddle:

      https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=331761416b63948b2828f4a9317423fc

        Attachments

          Activity

            People

            Assignee:
            marko Marko Mäkelä
            Reporter:
            sebi2020 Sebastian T
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration