1

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.

3

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');
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.