search

mmatuson / SchemaSync

A MySQL Schema Versioning and Migration Utility
http://mmatuson.github.io/SchemaSync/
Other
337 stars 110 forks source link

Colliding key names #18

Open russpoutine opened 12 years ago

russpoutine commented 12 years ago

Hi, I get a MySQL error (colliding index names) when trying to redefine a foreign key:

Following generated SQL fails:

ALTER TABLE `gallery_image`
DROP FOREIGN KEY `gallery_image_FK_3`
ADD CONSTRAINT `gallery_image_FK_3` FOREIGN KEY `gallery_image_FK_3` (`COLUMN_NAME`) 
REFERENCES  [more stuff here];

... with error message ...

ERROR 1005 (HY000) at line 26: Can't create table 'DATABASENAME.#sql-45c_e7' (errno: 121)

That error goes away if I split the statement in two ...

ALTER TABLE `gallery_image`
DROP FOREIGN KEY `gallery_image_FK_3`;

ALTER TABLE `gallery_image`
ADD CONSTRAINT `gallery_image_FK_3` FOREIGN KEY `gallery_image_FK_3` (`COLUMN_NAME`) 
REFERENCES  [more stuff here];

... so It seems that if both alterations are in a single statement - even though it first drops the key and then recreates it - the name still collides.