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

INSERT ON DUPLICATE KEY UPDATE foreign key fail

    XMLWordPrintable

Details

    Description

      Using INSERT ... ON DUPLICATE KEY UPDATE on a table which has a field in a foreign key linked to a PRIMARY field, throw a foreign key error even this field is not updated.

      To reproduce :

      create database test;
      use test;
       
      CREATE TABLE `v` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
       
      INSERT v values (1);
       
      CREATE TABLE `vp` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `v_id` int(10) unsigned NOT NULL,
        `p_id` int(10) unsigned NOT NULL,
        `ppp` varchar(255) NOT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`),
        KEY `FK_vp_v` (`v_id`),
        CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
       
      INSERT vp VALUES (12, 1, 100, 'text12');
       
      set sql_mode='';
       
      INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`);
      

      Result:

      ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`vp`, CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
      

      I can reproduce it on MariaDB 10.2.6 and 10.3.0.

      There is no error on 10.1.24.

      Thanks

      Attachments

        Issue Links

          Activity

            People

              jplindst Jan Lindström (Inactive)
              xorax Martin P
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.