0

MySQL 5.5

I have a simple InnoDB table:

CREATE TABLE `T` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `deleted` bigint(20) unsigned NOT NULL DEFAULT '0',
  `other` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`,`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

And another table that encodes relationships among entries from the first table:

CREATE TABLE `T_T` (
  `id1` bigint(20) unsigned NOT NULL,
  `deleted1` bigint(20) unsigned NOT NULL,
  `id2` bigint(20) unsigned NOT NULL,
  `deleted2` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id1`,`deleted1`,`id2`,`deleted2`),
  KEY `fk_T_T_2_idx` (`id2`,`deleted2`),
  CONSTRAINT `fk_T_T_2` FOREIGN KEY (`id2`, `deleted2`) REFERENCES `T` (`id`, `deleted`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_T_T_1` FOREIGN KEY (`id1`, `deleted1`) REFERENCES `T` (`id`, `deleted`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Creation of tables and population works perfectly fine.

INSERT INTO T (id, deleted, other) VALUES (1, 0, 'whatever');
INSERT INTO T_T (id1, deleted1, id2, deleted2) VALUES (1, 0, 1, 0);

Updating an entry in T, however, with something like this:

UPDATE T SET deleted = 1 WHERE id = 1;

leads to the following error:

ERROR 1452: 1452: Cannot add or update a child row: a foreign key constraint fails    (`test`.`T_T`, CONSTRAINT `fk_T_T_1` FOREIGN KEY (`id1`, `deleted1`) REFERENCES `T` (`id`, `deleted`) ON DELETE CASCADE ON UPDATE CASCADE)

As noted by others, this works if the entries in T_T don't refer to self (id1 != id2). But an UPDATE will fail when a row in T_T refers with both ids to the same row in T.

Is there a problem in MySQL with foreign keys that refer to another table's column(s) twice? Is there a work around for something like this?

12
  • SQLFiddle works fine
    – Alexander
    Feb 22, 2014 at 4:49
  • @Alexander You don't have any records in T_T that reference the foreign key that you modify.
    – Barmar
    Feb 22, 2014 at 4:52
  • Here's a fiddle that demonstrates it working fine: sqlfiddle.com/#!2/1574c9/1
    – Barmar
    Feb 22, 2014 at 4:54
  • This one shows that the update cascaded properly: sqlfiddle.com/#!2/1574c9/2
    – Barmar
    Feb 22, 2014 at 4:55
  • also works on my 5.0.77 version
    – Alexander
    Feb 22, 2014 at 4:56

0