Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
11.8.9
-
None
-
None
-
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.