Error: 6125 MySQL 8.4.0の非互換で親テーブルでUNIQUE KEYがついてないとForeign Keyが作れなくなった？

TL;DR

• FOREIGN KEY (col1, col2) REFERENCES table_name (col3, col4) の親側のカラムリスト、←で言うなら (col3, col4) がユニーク制約がかかっていないと外部キー制約が作れなくなった？

  • 親に (col3, col4, col5) のユニークキーがあってもダメ。きっちり一致しないといけない。

  • 親に (col3) のユニークキーがあれば (col3, col4) も当然一意になるはずだけれどこの指定もダメ。

• 正規化の過程でできあがるテーブル分割とFOREIGN KEYは自然とPRIMARY KEYとの結合になるから問題ないだろうけれど、それ以外でFK使っている場合は8.4で通らなくなるかも

---

### 親テーブル。 numはインデックスつきだけどPRIMARYでもUNIQUEでもない

mysql84 11> CREATE TABLE d2.t1 (num int, KEY(num));

Query OK, 0 rows affected (0.02 sec)

### 子テーブル。子テーブル側はセカンダリキーでもユニークキーでも関係なしに親がユニークキーじゃないからError 6125
mysql84 11> CREATE TABLE d2.t2 (num int, UNIQUE KEY(num), FOREIGN KEY (num) REFERENCES d2.t1(num)) Engine InnoDB;
ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 't2_ibfk_1' in the

親テーブル側で (val, num) のユニークキーを作る。

### t1作り直し
mysql84 11> DROP TABLE t1;
Query OK, 0 rows affected (0.02 sec)

mysql84 11> CREATE TABLE t1 (num bigint, val varchar(32), PRIMARY KEY(val), UNIQUE KEY(val, num));
Query OK, 0 rows affected (0.02 sec)

### ユニークキーがあっても一部(右側)しか使ってないとダメ
mysql84 11> CREATE TABLE d2.t2 (num bigint, KEY(num), FOREIGN KEY (num) REFERENCES d2.t1(num)) Engine InnoDB;
ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 't2_ibfk_1' in the referenced table 't1'

### じゃあ左端が一致すればいいのかと思うと
mysql84 11> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql84 11> CREATE TABLE t1 (num int, val varchar(32), PRIMARY KEY(num, val));
Query OK, 0 rows affected (0.01 sec)

### やっぱり完全にPRIMARYまたはUNIQUEに一致しないとダメらしい
mysql84 11> CREATE TABLE t2 (num int, FOREIGN KEY(num) REFERENCES t1(num));
ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 't2_ibfk_1' in the referenced table 't1'

PRIMARY KEY(num) ならば (num, val) がユニークになるのは自明だけれどそれでもダメらしい。

mysql84 11> CREATE TABLE t1 (num int, val varchar(32), PRIMARY KEY(num));
Query OK, 0 rows affected (0.02 sec)

mysql84 11> CREATE TABLE t2 (num int, val varchar(32), FOREIGN KEY(num, val) REFERENCES t1(num, val));
ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 't2_ibfk_1' in the referenced table 't1'

リリースノートにincompatibleって記載がないので仕様なのか間違ったのかわからないけれど、仕様だったら親側にユニーク制約をつける以外に解決法はなさげ。

mysql80 8> CREATE DATABASE d1;
Query OK, 1 row affected (0.01 sec)

mysql80 8> CREATE TABLE d1.t1 (num int, KEY(num));
Query OK, 0 rows affected (0.03 sec)

mysql80 8> CREATE TABLE d1.t2 (num int, KEY(num), FOREIGN KEY (num) REFERENCES d1.t1(num));
Query OK, 0 rows affected (0.03 sec)

mysql80 8> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.37    |
+-----------+
1 row in set (0.01 sec)

なお、8.0だとこれは通る。バージョンアップの時に注意（8.0でこの形のFKを作ってあるテーブルを持った状態でインプレースアップグレードするとどうなるんだろう…？ 🤔

→ 【2024/05/05 17:09】特に何の問題もなく8.0と同じ（UNIQUEじゃなくても使えるまま）まま動いた

【2024/05/05 17:02】

バグレポートした

MySQL Bugs: #114882: Unexpected incompatibility in InnoDB Foreign Key constraint with Error 6125

【2024/05/16 14:02】

公式に非互換のアナウンスはないけどこのパラメーターで変えられるらしい

https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_restrict_fk_on_non_standard_key

mysql84 20> SELECT @@RESTRICT_FK_ON_NON_STANDARD_KEY;+-----------------------------------+
| @@RESTRICT_FK_ON_NON_STANDARD_KEY |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

​