MySQLの外部キー制約の付与でハマったのでメモ

長らく投稿できていませんでしたが、MySQLの外部キー制約の付与で予想以上にハマってしまってので 備忘のためメモを残しておきます。

説明の簡略化のため、簡単なテーブル構造に置き換えて説明します。

状況

以下のような２つのテーブルがあったとします。

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| organizations  |
| users          |
+----------------+

それぞれ、Stringのuuid をPrimary keyとしてもち、 usersはorganization_id を持てるようにしたいとします。

mysql> desc users;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| uuid            | varchar(255) | NO   | PRI | NULL    |       |
| name            | varchar(255) | YES  |     | NULL    |       |
| organization_id | varchar(255) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

mysql> desc organizations;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
| uuid  | varchar(255) | NO   | PRI | NULL    |       |
+-------+--------------+------+-----+---------+-------+

外部キー制約をつけるため、以下のSQLを実行しました

ALTER TABLE users ADD CONSTRAINT fk_test_1 FOREIGN KEY(organization_id) REFERENCES organization(uuid);

ところが、以下のようにエラーが出てしまい、失敗しました。

 ERROR 1215 (HY000): Cannot add foreign key constraint

organizatons.uuid も users.organization_id も同じ varchar(255) なのに 何で失敗するんだ...!と 数時間悩んでしまいました。

結局、show create table で構造を見たら解決しました。

mysql> show create table users;
| users | CREATE TABLE `users` (
  `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

mysql> show create table organizations;
| organizations | CREATE TABLE `organizations` (
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uuid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

なんと、charset が異なっていたのです。ですので、 同じカラムとして見なされず、外部キー制約でエラーとなっていたのでした。

背景

実はこれ、Railsのマイグレーション時に発生したエラーで、開発環境は今までオッケーでしたが、構築中の別環境のマイグレーションでのみ発生しておりました。Rails は 5系のどこかからデフォルトで utf8mb4を使用するようになっており、Railsのバージョンアップに起因してDefault charset も変わってしまったのが原因です。しかしその場ですぐ影響が出ず、異なる文字コードのテーブル間で外部キー制約を貼ろうとしたタイミングでエラー発生しました。

詳しい人から見たらすぐにわかる事象かもしれないですが、エラーメッセージからなかなか原因に行き着かず、結構悩んでしまったので、メモとして残しておきます。