MySQLでカラムを追加して外部キーを付け替えるときの手順

状況

以下のような状況を想定して外部キー付け替えの手順をメモします。

users テーブルには id , uid の2カラムがあります。
posts テーブルには user_id カラムがあり、これが users.id を参照する外部キーとなっています。
また、 posts テーブルには uid カラムがありません。

ここで、 posts テーブルに uid カラムを追加し、これを users.id を参照する外部キーに設定し、既存の posts.user_id はカラムごと削除します。

手順

1. posts テーブルに uid カラムの作成

ALTER TABLE posts ADD uid VARCHAR(255);

2. posts.uid カラムに値を挿入

外部キーを貼った際に整合性の取れないデータが入っているとエラーになるので先に値を入れておきます。

UPDATE posts, users SET posts.uid = users.uid WHERE posts.user_id = users.id;

3. 既存の posts.user_id の外部キー制約を解除する

SHOW CREATE TABLE posts;

を実行します。中に、

CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)

のような行があるので posts_ibfk_1 の値をメモします。
以下を実行して外部キー制約を外します。

ALTER TABLE posts DROP FOREIGN KEY posts_ibfk_1;

4. posts.user_id カラムを削除する

外部キーの制約が外れているので普通に削除できます。

ALTER TABLE posts DROP user_id;

5. posts.uid の外部キーの設定を行う

ALTER TABLE posts ADD FOREIGN KEY (uid) REFERENCES users (uid);