tiistai 14. syyskuuta 2010

MySQL changes foreign key names

Just spent few hours debugging database conversion scripts, and bumped into a "little" bug in MySQL.


CREATE TABLE `test1`(
`id` integer NOT NULL,
PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `test2` (
`id` integer NOT NULL,
`id2` integer DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IX_test_1` (`id`),
CONSTRAINT `FK_1` FOREIGN KEY (`id2`) REFERENCES `test1` (`id`) ) ENGINE=INNODB DEFAULT CHARSET=latin1;


INSERT INTO test2 (id, id2) VALUES (1, 44);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`test2`, CONSTRAINT `FK_1` FOREIGN KEY (`id2`) REFERENCES `test1` (`id`))


ALTER TABLE test2 DROP FOREIGN KEY `FK_1`;
ALTER TABLE test2 ADD FOREIGN KEY `FK_1` (`id2`) REFERENCES `test1` (`id`);


INSERT INTO test2 (id, id2) VALUES (1, 44);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`test2`, CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `test1` (`id`))

Somehow foreign key named FK_1 changed into test2_ibfk_1..