Migrations: What's the right way to rename a Foreign Key?

Question

I'm writing a migration for one of my plugins, and struggling to resolve something which should theoretically be simple:

$this->renameColumn('myplugin_mytable', 'oldId', 'newId');

Except that doesn't work, because the column is a foreign key. The migration fails, and it kicks out this error in the log file:

2016/02/07 20:09:29 [error] [application] CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1025 Error on rename of './mysite/#sql-95d9_1e' to './mysite/craft_myplugin_mytable' (errno: 150). The SQL statement executed was: ALTER TABLE craft_myplugin_mytable CHANGE oldId newId int(11) NOT NULL (/craft/app/framework/db/CDbCommand.php:358)

Googling around, it seems that this error happens when you attempt to alter a foreign key.

I tried this method as well:

1. dropForeignKey for "oldId"
2. renameColumn from "oldId" to "newId"
3. addForeignKey for "newId"

But unfortunately, that's not working smoothly either.

What is the right way to rename a foreign key?

---

Sidenote: I'm tempted to post a feature request for P&T to add a renameForeignKey method to the MigrationHelper class. If that seems like the right approach, I'm happy to submit that feature request and we can close this question.

Answer 1

The 2nd method you tried is the correct order to do it in.

1. Call BaseMigration->dropForeignKey() ($this->dropForeignKey from within the context of a migration).
2. Call BaseMigration->renameColumn() ($this->renameColumn).
3. And add the column back with with BaseMigration->addForeignKey() ($this->addForeignKey).

If you received an error during any of those steps, it's likely because MySQL is complaining about inconsistent data among the FK endpoints or the endpoints are of different data types.

---

EDIT BY OP

The final version of the code looked like this:

$table = 'myplugin_mytable';
$this->dropForeignKey($table, 'oldId');
$this->renameColumn($table, 'oldId', 'newId');
$this->addForeignKey($table, 'newId', 'myplugin_related', 'id', 'CASCADE', 'CASCADE');