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

Multi column foreign key on same table fails depending on primary key definition

    XMLWordPrintable

Details

    • Can result in unexpected behaviour

    Description

      The following table definntion

      CREATE TABLE `test` ( `id_art` int(10) unsigned NOT NULL, `old_id_full` int(10) unsigned NOT NULL, `old_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id_art`,`old_id_full`,`old_id`), KEY `ttt1` (`id_art`,`old_id`), CONSTRAINT `tttt1` FOREIGN KEY (`id_art`,`old_id_full`) REFERENCES `test` (`id_art`,`old_id`) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci CHECKSUM=1 ROW_FORMAT=COMPRESSED;

      with

      insert into test values(1,1,1);

      returns

      ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`diss`.`test`, CONSTRAINT `tttt1` FOREIGN KEY (`id_art`, `old_id_full`) REFERENCES `test` (`id_art`, `old_id`) ON DELETE CASCADE ON UPDATE CASCADE)

      while this table definntion

      CREATE TABLE `test` ( `id_art` int(10) unsigned NOT NULL, `old_id_full` int(10) unsigned NOT NULL, `old_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id_art`,`old_id`), UNIQUE KEY `ttt1` (`id_art`,`old_id_full`,`old_id`), CONSTRAINT `tttt1` FOREIGN KEY (`id_art`,`old_id_full`) REFERENCES `test` (`id_art`,`old_id`) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci CHECKSUM=1 ROW_FORMAT=COMPRESSED;

      does not return any error (this is the expected behavior). So it seems it depends on the third column for the PK definition in the first example, or am I just missing something?

      Thank you.

      Attachments

        Activity

          People

            Unassigned Unassigned
            HansB Hans B
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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