How can I programmatically replace/update an automatically-named MySQL Foreign Key?

Question

I am writing a script to upgrade an old version of my schema to a newer version. The script is a series of MySQL commands within an .sql file.

One of the changes I need to make is to recreate a foreign key following the renaming of the table to which it refers. e.g.:

CREATE TABLE foo (
   `key` INT UNSIGNED NOT NULL,

   PRIMARY KEY (`key`)
) ENGINE=InnoDB;

CREATE TABLE `bar` (
   `key` INT UNSIGNED NOT NULL,
   `fk` INT UNSIGNED NOT NULL,

   PRIMARY KEY (`key`),
   FOREIGN KEY (`fk`) REFERENCES `foo` (`key`) ON DELETE CASCADE
) ENGINE=InnoDB;

becomes:

CREATE TABLE `foo2` (
   `key` INT UNSIGNED NOT NULL,

   PRIMARY KEY (`key`)
) ENGINE=InnoDB;

CREATE TABLE `bar` (
   `key` INT UNSIGNED NOT NULL,
   `fk` INT,

   PRIMARY KEY (`key`),
   FOREIGN KEY `fk1` (`fk`) REFERENCES `foo2` (`key`) ON DELETE CASCADE
) ENGINE=InnoDB;

Renaming foo to foo2 is easy, but updating that foreign key in bar is not so much. I cannot simply name the foreign key in an ALTER TABLE statement because the old schema failed to name it, and I do not wish to try to "guess" at the automatically-generated substitute.

I found the following which will generate the statement I need in order to drop the key entirely (later recreating it as needed):

SELECT CONCAT(
   'ALTER TABLE ',
   `table_schema`, '.', `table_name`,
   ' DROP FOREIGN KEY ', `constraint_name`, ';'
)
FROM `information_schema`.`table_constraints`
WHERE `constraint_type` = 'FOREIGN KEY'
AND `table_name` = 'bar';

but how can I actually execute the result of that, from within my upgrade script? Or can I solve the problem better?

@Sirko: I saw that but nothing seemed to apply. Either it wasn't applicable programmatically or it was silly (copying the entire table away and back again). However, I'd missed Dandalf's solution, which is rather good. — Lightness Races in Orbit, Jan 08 '15 at 17:11
You still can't rename so you'll have to drop/re-create. [You can do it in one statement](http://stackoverflow.com/questions/6188011/how-do-i-rename-a-foreign-key-in-mysql) and [execute it dynamically](http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure). — Ben, Jan 09 '15 at 14:11
@Ben: That latter link does not do the job. It cannot dynamically generate the statement from the result of a query. The former link also does not do the job, because it requires you to _name the constraint_, and the entire problem here is that it doesn't have a predictable name! — Lightness Races in Orbit, Jan 09 '15 at 14:25
VTC as dupe of http://stackoverflow.com/q/1323462/560648, but again note that the accepted answer _doesn't fit_! Only Dandalf's does. — Lightness Races in Orbit, Jan 09 '15 at 14:26
Have you been able to solve the issue? With the above options I think you can accomplish what you need. — wchiquito, Jan 12 '15 at 11:51
For schema change management in the future, have a look at [Sqitch](http://sqitch.org/). — Schwern, Mar 02 '15 at 05:58
@Schwern: Not too bad. Doesn't look all that different from what we're already doing, mind you. — Lightness Races in Orbit, Mar 02 '15 at 10:46

How can I programmatically replace/update an automatically-named MySQL Foreign Key?

0 Answers0