<p>The 2nd method you tried is the correct order to do it in.</p>

<ol>
<li>Call <a href="https://craftcms.com/classreference/etc/db/BaseMigration#dropForeignKey-detail" rel="nofollow">BaseMigration->dropForeignKey()</a> (<code>$this-&gt;dropForeignKey</code> from within the context of a migration).</li>
<li>Call <a href="https://craftcms.com/classreference/etc/db/BaseMigration#renameColumn-detail" rel="nofollow">BaseMigration->renameColumn()</a> (<code>$this-&gt;renameColumn</code>).</li>
<li>And add the column back with with <a href="https://craftcms.com/classreference/etc/db/BaseMigration#addForeignKey-detail" rel="nofollow">BaseMigration->addForeignKey()</a> (<code>$this-&gt;addForeignKey</code>).</li>
</ol>

<p>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.</p>

<hr>

<p><strong>EDIT BY OP</strong></p>

<p>The final version of the code looked like this:</p>

<pre class="lang-php prettyprint-override"><code>$table = 'myplugin_mytable';
$this-&gt;dropForeignKey($table, 'oldId');
$this-&gt;renameColumn($table, 'oldId', 'newId');
$this-&gt;addForeignKey($table, 'newId', 'myplugin_related', 'id', 'CASCADE', 'CASCADE');
</code></pre>

CC BY-SA 3.0
2016-02-08T04:22:54.877Z
2016-02-08T07:10:51.087Z
2016-02-08T07:10:51.087Z
Brad Bell
<p>I'm writing a migration for one of my plugins, and struggling to resolve something which should theoretically be simple:</p>

<pre class="lang-php prettyprint-override"><code>$this-&gt;renameColumn('myplugin_mytable', 'oldId', 'newId');
</code></pre>

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

<blockquote>
  <p>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 <code>craft_myplugin_mytable</code> CHANGE <code>oldId</code> <code>newId</code> int(11) NOT NULL (/craft/app/framework/db/CDbCommand.php:358)</p>
</blockquote>

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

<p>I tried this method as well:</p>

<ol>
<li><code>dropForeignKey</code> for "oldId"</li>
<li><code>renameColumn</code> from "oldId" to "newId"</li>
<li><code>addForeignKey</code> for "newId"</li>
</ol>

<p>But unfortunately, that's not working smoothly either.</p>

<p><strong>What is the right way to rename a foreign key?</strong></p>

<hr>

<p>Sidenote: I'm tempted to post a feature request for P&amp;T to add a <code>renameForeignKey</code> method to the <a href="https://craftcms.com/classreference/helpers/MigrationHelper" rel="nofollow">MigrationHelper</a> class. If that seems like the right approach, I'm happy to submit that feature request and we can close this question.</p>

<plugin-development><migration>
Migrations: What's the right way to rename a Foreign Key?
# Migrations: What's the right way to rename a Foreign Key?

(Asked by: username_0 on 2016-02-07)

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:

dropForeignKey for "oldId"
renameColumn from "oldId" to "newId"
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.

**Tags:** plugin-development, migration


---

## 1 answers

### Answer by username_1 on 2016-02-08. Score: 3 | Accepted answer

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

Call BaseMigration->dropForeignKey() ($this->dropForeignKey from within the context of a migration).
Call BaseMigration->renameColumn() ($this->renameColumn).
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');


---

