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

INSERT ON DUPLICATE KEY UPDATE foreign key fail

    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

              • Assignee:
                jplindst Jan Lindström
                Reporter:
                xorax Martin P
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: