MySQL 8.4の外部キーで指定する参照先カラムには一意インデックスが設定されている必要がある。または、参照先カラムが主キーの必要がある。

以前の挙動について

SQL標準や他のRDBMSに慣れている方は「普通の事ではないか」という感想を持たれると思う。
　非標準的な事だが、MySQL 8.0 innoDBだと下記のように、一意インデックスが無い・主キーでないカラムを参照する外部キーを定義できた。

CREATE TABLE students
(
  student_id INT,
  name VARCHAR(40),
  class_id INT,
  PRIMARY KEY (student_id),
  INDEX (class_id)
) ENGINE = InnoDB
;

CREATE TABLE teachers
(
  teacher_id INT,
  name VARCHAR(40),
  class_id INT,
  PRIMARY KEY (teacher_id)
) ENGINE = InnoDB
;

-- fails in MySQL 8.4.0 or later.
ALTER TABLE teachers
 ADD CONSTRAINT fk_teachers_class_id
 FOREIGN KEY (class_id)
 REFERENCES students(class_id)
 ON DELETE CASCADE ON UPDATE CASCADE;

MySQL 8.4での挙動

MySQL 8.4.0では上述のような外部キーが非推奨になった。

The use of non-unique or partial keys as foreign keys is deprecated in MySQL.

同じSQLを実行すると、エラーが起きる。

ERROR 6125 (HY000) at line 45: Failed to add the foreign key constraint. Missing unique key for constraint 'fk_teachers_class_id' in the referenced table 'students'

リンク先に記載されている通り、mysqldを起動するときに --skip-restrict-fk-on-non-standard-key オプション（非推奨）を指定した場合、同じSQL実行が正常に終了する。

一意でないキーを参照する外部キーはどのように動くか

上述のような外部キーについて以前から、公式に次の通り説明されていた。

一意でないキーまたは NULL 値を含むキーへの外部キー参照の処理は、UPDATE や DELETE CASCADE などの操作に対して適切に定義されていません。 UNIQUE (PRIMARY を含む) および NOT NULL キーのみを参照する外部キーを使用することをお勧めします。

　DELETE CASCADEなどが「適切に定義されて」いない。実際は、どのように動作するのだろうか？　下記の確認用ソースコードで敢えて非推奨の設定を行なって試してみた。

確認用ソースコード

フォルダ構成

|--docker-compose.yml
|--initdb
    |--01.sql

docker-compose.yml


version: '3.1'

services:
  db:
    image: mysql:8.4.0
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example
    command:
      - "mysqld"
      - "--skip-restrict-fk-on-non-standard-key"
    ports:
      - 3306:3306
    volumes:
      - ./initdb:/docker-entrypoint-initdb.d

01.sql

DROP SCHEMA IF EXISTS example
;
CREATE SCHEMA example
;
USE example
;

DROP TABLE IF EXISTS students
;
CREATE TABLE students
(
  student_id INT,
  name VARCHAR(40),
  class_id INT,
  PRIMARY KEY (student_id),
  INDEX (class_id)
) ENGINE = InnoDB
;
INSERT INTO students values
(101,"Kelvin Cook",1),
(102,"Gwen Sanford",1),
(103,"Wyatt Roy",1),
(201,"Anjali Reyes",2),
(202,"Clyde Rose",2),
(203,"Lara Bender",2)
;

DROP TABLE IF EXISTS teachers
;
CREATE TABLE teachers
(
  teacher_id INT,
  name VARCHAR(40),
  class_id INT,
  PRIMARY KEY (teacher_id)
) ENGINE = InnoDB
;
INSERT INTO teachers values
(901,"Tristan Johns",1),
(902,"Gwen Sanford",2)
;

-- need --skip-restrict-fk-on-non-standard-key
-- in MySQL 8.4.0 or later.
ALTER TABLE teachers ADD CONSTRAINT fk_teachers_class_id FOREIGN KEY (class_id) REFERENCES students(class_id) ON DELETE CASCADE ON UPDATE CASCADE;

DELETE FROM students WHERE student_id = 203;

削除前のデータ

studentテーブル

student_id	name	class_id
101	Kelvin Cook	1
102	Gwen Sanford	1
103	Wyatt Roy	1
201	Anjali Reyes	2
202	Clyde Rose	2
203	Lara Bender	2

teacherテーブル

teacher_id	name	class_id
901	Tristan Johns	1
902	Gwen Sanford	2

　外部キーが定義されているので、teacherテーブルに class_id=2 のデータを登録する時、studentテーブルにclass_id=2 のデータが存在する必要がある。

削除後のデータ

studentテーブル

student_id	name	class_id
101	Kelvin Cook	1
102	Gwen Sanford	1
103	Wyatt Roy	1
201	Anjali Reyes	2
202	Clyde Rose	2

teacherテーブル

teacher_id	name	class_id
901	Tristan Johns	1

(student_id=203, class_id=2)のデータを削除した時、DELETE CASCADEによって (teacher_id=902, class_id=2) のデータが自動的に削除された。

　(student_id=203, class_id=2) を削除した時、studentテーブルには他の class_id=2 のデータが残っている。したがってteacherテーブルの(teacher_id=902, class_id=2)のデータは外部キーに関する制約を満たしている。しかしDELETE CASCADEで削除された。（直感に反する結果だ。）