MySQL 8.4（LTS）では、外部キー制約（FOREIGN KEY）の定義ルールに関して、デフォルトの仕様がこれまでから厳格化しました。具体的には、外部キーが参照する親テーブル側のカラムに、非一意キー（ユニークインデックスまたは主キーではないキー）の場合にDDLがエラーになるようになっています。

これまで動いていたDDLが通らなくなるケースがあり、スキーマ管理やデータマイグレーションで思わぬ影響を受けることがあります。本稿では、この変更の前と後の挙動、そして既存環境への影響と対応策について紹介します。

非一意キーを参照する外部キー

MySQLでは長らく、外部キー参照先（親テーブル）のカラムがユニークでなくても、外部キーを貼ることが可能でした。これはSQL標準（ISO/IEC 9075-2: Foundation）における仕様とは異なるInnoDB独自の拡張（non-standard foreign key）です。

SQL標準では、外部キーが参照できるのは「親テーブルに存在する主キーまたはユニークキー」に限られます。つまり、子テーブルの1行は必ず親テーブルの1行に一意に対応する必要があります。

MySQLでは互換性や柔軟性を優先し、これを長らく緩和していました。そのため、次のようなDDLでもエラーにならず、定義が通っていたのです。

たとえば、以下のような場合でもCREATE TABLEが通っていました。

mysql> CREATE TABLE parent (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code INT,
  INDEX idx_code (code)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE child (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  parent_code INT,
  FOREIGN KEY (parent_code) REFERENCES parent(code)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

parent.codeはユニークではないため、child.parent_codeの親行は複数存在する可能性があります。以下のようなデータを入れると、削除や更新時にエラーになります。

mysql> SELECT * FROM parent;
+----+------+
| id | code |
+----+------+
|  1 |  100 |
|  2 |  100 |
|  3 |  200 |
+----+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM child;
+----+-------------+
| id | parent_code |
+----+-------------+
|  1 |         100 |
+----+-------------+
1 row in set (0.01 sec)

// parent_code = 100 と同じ値の code を持つ parent 行の DELETE はエラーとなる
mysql> DELETE FROM parent WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`d1`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_code`) REFERENCES `parent` (`code`))

mysql> DELETE FROM parent WHERE id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`d1`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_code`) REFERENCES `parent` (`code`))

// parent_code = 100 と同じ値の code を持つ parent テーブル行の code 値変更もエラーとなる
mysql> UPDATE parent SET code = 101 WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`d1`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_code`) REFERENCES `parent` (`code`))

mysql> UPDATE parent SET code = 101 WHERE id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`d1`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_code`) REFERENCES `parent` (`code`))

非一意キーを参照する外部キー制約は標準SQLではなく、現在非推奨となっているInnoDB拡張機能です。MySQL 8.4からは、デフォルトではそのような非標準キーの作成が禁止されました。

restrict_fk_on_non_standard_key

非標準キーの作成禁止を制御するためにMySQL 8.4から追加されたパラメータがrestrict_fk_on_non_standard_keyです。デフォルトではONとなっており、非標準キーは作成できません。

試しにMySQL 8.4.5で非標準キーを含むテーブル作成を試みると、以下のようにエラーとなります。

mysql> CREATE TABLE parent (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code INT,
  INDEX idx_code (code)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE child (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  parent_code INT,
  FOREIGN KEY (parent_code) REFERENCES parent(code)
) ENGINE=InnoDB;
ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'child_ibfk_1' in the referenced table 'parent'

restrict_fk_on_non_standard_key=OFFに設定することで、MySQL 8.4未満と同様、非一意キー参照を許可できます。ただし警告が発生し、今後削除予定であることが明示されます。

mysql> SET restrict_fk_on_non_standard_key=OFF;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE child (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  parent_code INT,
  FOREIGN KEY (parent_code) REFERENCES parent(code)
) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

Warning (Code 6124): Foreign key 'child_ibfk_1' refers to non-unique key or partial key. This is deprecated and will be removed in a future release.

移行時の影響と対応策

MySQL 8.0 → 8.4のアップグレードでは、既存に非標準キーを持つスキーマがあってもエラーにはなりません。アップグレード時に警告として検出されるだけで、実行は継続されます。

ただし、新たにテーブルを作成・変更する場合には制約がかかります。したがって、CI/CDパイプラインで自動的にDDL検証を行っている場合や、サードパーティ製品によってDDLが自動実行される場合は、以下のような対策が必要です。

• 非ユニークな外部キーを許可する場合は、一時的にrestrict_fk_on_non_standard_key=OFFを設定
• 本番環境では常にONとし、非標準設計を検出したら修正する運用を確立する

まとめ

MySQL 8.4未満ではSQL非標準の仕様として、非一意キーを参照する外部キーが作成可能でした。MySQL 8.4以降では、restrict_fk_on_non_standard_keyによりそのような非標準キーの作成がデフォルトで禁止されました。互換性を保つためにOFFにすることは可能ですが、非標準キーのサポートは将来のMySQLバージョンで削除される見込みです。

したがって、8.4以降への移行にあたっては、外部キー参照先をすべて主キーまたはユニークキーに統一することが推奨されます。

参考資料

• 15.1.20.5 FOREIGN KEY Constraints
• 1.7.2.3 FOREIGN KEY Constraint Differences
• restrict_fk_on_non_standard_key
• Changes in MySQL 8.4.0 (2024-04-30, LTS Release)