Search code examples
mysqlconstraintsinnodbdatabase-indexes

How to rename foreign key constraint | MySql 5.6, InnoDB


I need to rename fk constraints (drop all fk, create new ones), but MySql (cuz InnoDB engine) created indexes for these constraints. Do I need to drop indexes manually? Or it will lead to issues? Please, somebody describe me this point.


Solution

  • It will not lead to issues.

    Adding an FK should discover that there is already a sufficient index and not add a second one. Even if it adds a redundant index, little harm is done.

    I wonder -- Why do you care what the constraint names are?

    From 5.6 doc: "For ALTER TABLE, unlike CREATE TABLE, ADD FOREIGN KEY ignores index_name if given and uses an automatically generated foreign key name. As a workaround, include the CONSTRAINT clause to specify the foreign key name: ADD CONSTRAINT name FOREIGN KEY (....) .." and "Adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE ... ALGORITHM=INPLACE but not for ALTER TABLE ... ALGORITHM=COPY."

    My point in bringing that up is that ALTER usually runs faster if you throw all the changes to a table into a single statement. But DROP + ADD is disallowed in the COPY case.