外部キーの設定
テーブル構成
学生の履修科目に関する簡単なデータベースを考える。
studentsテーブルは学生に関するデータを保存- 学生ID(主キー)
- 学生の名前
student_coursesテーブルは学生の履修科目に関するデータを保存- 履修科目ID(主キー)
- 学生ID
- 科目名
学生テーブル
以下のクエリーでテーブルを作成し、学生データを登録。
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(10) ); INSERT INTO students (id, name) VALUES (1, '安藤'), (2, '伊藤'), (3, '宇藤'); |
登録結果の確認。
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT * FROM students; +----+--------+ | id | name | +----+--------+ | 1 | 安藤 | | 2 | 伊藤 | | 3 | 宇藤 | +----+--------+ 3 rows in set (0.00 sec) |
履修科目テーブル～外部キーの設定
以下のクエリーで履修科目テーブルを作成。student_idを外部キーとして、studentsテーブルのid(学生ID)を参照している。
外部キーを設定するカラムについて、以下のように定義する。
FOREIGN_KEY (参照するキー) REFERENCES 参照されるテーブル(参照されるキー)
|
1 2 3 4 5 6 |
CREATE TABLE student_courses ( id INT PRIMARY KEY, student_id INT, FOREIGN KEY (student_id) REFERENCES students(id), course_name VARCHAR(10) ); |
テーブル構造を確認すると、student_idのKey欄がMULとなる。このMULについてはぴったりの情報が得られなかったが、以下のようなことらしい。
- 外部参照するキーにはインデックスが設定される
PRIMARY KEYやINIQUEが設定されていないインデックスは同じ値を取り得る- なので
multiple keyでMULと表示される
|
1 2 3 4 5 6 7 8 9 |
mysql> DESCRIBE student_courses; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | student_id | int | YES | MUL | NULL | | | course_name | varchar(10) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) |
外部キー制約の確認
正常な登録
新しいデータをstudent_coursesテーブルに登録する。student_idとして登録済みのstudents.id = 1の学生IDを指定する。
|
1 |
INSERT INTO student_courses (id, student_id, course_name) VALUES (1, 1, '数学'); |
正常に登録されて、students_coursesテーブルは以下のようになる。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> INSERT INTO student_courses (id, student_id, course_name) VALUES (1, 1, '数学'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM student_courses; +------+------------+-------------+ | id | student_id | course_name | +------+------------+-------------+ | 1 | 1 | 数学 | +------+------------+-------------+ 1 row in set (0.00 sec) |
存在しないデータの登録はエラー
student_idに、studentsテーブルでは登録されていない値を指定する。
|
1 |
INSERT INTO student_courses (id, student_id, course_name) VALUES (2, 4, '数学'); |
外部キー制約でエラーとなる。
|
1 2 3 4 5 |
mysql> INSERT INTO student_courses (id, student_id, course_name) VALUES (2, 4, '数学'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`student_courses`, CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)) mysql> |
参照されている親のデータは消せない
student_coursesテーブルのデータから参照されている、studentsテーブルのid=1のデータを削除しようとするとエラーになる。
|
1 2 3 4 |
mysql> DELETE FROM students WHERE id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`student_courses`, CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)) |
参照されていない親のデータは削除できる
|
1 2 |
mysql> DELETE FROM students WHERE id=2; Query OK, 1 row affected (0.01 sec) |
student_coursesのデータから参照されていない、studentsテーブルのid=2のデータは削除できる。
|
1 2 3 4 5 6 7 8 |
CREATE TABLE student_courses ( id INT, student_id INT, FOREIGN KEY fk_student_id(student_id) REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE, course_name VARCHAR(10) ); |
不整合時の挙動～RESTRICTやCASCADEなど
FOREIGN KEY指定時に、参照先のデータの削除時と変更時の挙動を指定できる。
ON DELETE 挙動指定 ON UPDATE 挙動指定
挙動指定には以下の4つがあり、指定しない場合はRESTRICTとなる。
- RESTRICT
- DELETE, UPDATEともエラーになる。
- CASCADE
- DELETEでは参照元のデータも削除され、UPDATEでは参照先の変更が参照元のデータに反映される。
- SET NULL
- DELETE, UPDATEともNULLに置き換わる。
- NO ACTION
- RESTRICTと同じ挙動。
外部キー制約をCASCADEに変更する
CREATE TABLEの確認
外部キー制約設定後のテーブル定義を以下のコマンドで確認してみる。
SHOW CREATE TABLE テーブル名\G;
- 最初に実行したクエリーの内容に即している
CONSTRAINT `外部制約名`が追加されている
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SHOW CREATE TABLE student_courses\G *************************** 1. row *************************** Table: student_courses Create Table: CREATE TABLE `student_courses` ( `id` int NOT NULL, `student_id` int DEFAULT NULL, `course_name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `student_id` (`student_id`), CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
挙動の変更
外部キー制約の挙動をRESTRICTからCASCADEに変更する。外部キーの操作はALTER TABLEで行うが、一度に変更できないので一旦外部キーを削除し、新たな条件で外部キーを追加する。
外部キーの削除
外部キー削除のコマンドは以下の通り。
ALTER TABLE 参照元テーブル DROP FOREIGN KEY 外部制約名;
外部制約名は、先のSHOW CREATE TABLEで確認した内容で指定する。
|
1 2 3 |
mysql> ALTER TABLE student_courses DROP FOREIGN KEY student_courses_ibfk_1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 |
外部キー削除後もインデックスは残っている。
|
1 2 3 4 5 6 7 8 9 |
mysql> DESCRIBE student_courses; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | student_id | int | YES | MUL | NULL | | | course_name | varchar(10) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) |
外部キー削除後のCREATE TABLEを確認すると、student_idにインデックスが設定されている。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SHOW CREATE TABLE student_courses\G *************************** 1. row *************************** Table: student_courses Create Table: CREATE TABLE `student_courses` ( `id` int NOT NULL, `student_id` int DEFAULT NULL, `course_name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `student_id` (`student_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
外部キーの追加
以下のクエリーで削除・更新時の挙動を指定して外部キーを追加。
|
1 2 3 4 |
ALTER TABLE student_courses ADD FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE; |
クエリー実行。
|
1 2 3 4 5 6 |
mysql> ALTER TABLE student_courses -> ADD FOREIGN KEY (student_id) REFERENCES students(id) -> ON DELETE CASCADE -> ON UPDATE CASCADE; Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 |
CREATE TABLE確認。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SHOW CREATE TABLE student_courses\G *************************** 1. row *************************** Table: student_courses Create Table: CREATE TABLE `student_courses` ( `id` int NOT NULL, `student_id` int DEFAULT NULL, `course_name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `student_id` (`student_id`), CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
CASCADEの挙動
ON UPDATE CASCADE
参照先、親テーブルstudentsのデータのid=3を9に変更する。RESTRICTではエラーになったが、CASCADEの場合は変更が通る。
|
1 2 3 |
mysql> UPDATE students SET id = 9 WHERE id = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
student_coursesのstudent_idも変更されている。
|
1 2 3 4 5 6 7 8 9 10 |
+----+------------+--------+-------------+ | id | student_id | name | course_name | +----+------------+--------+-------------+ | 1 | 1 | 安藤 | 数学 | | 2 | 1 | 安藤 | 物理学 | | 3 | 2 | 伊藤 | 数学 | | 4 | 9 | 宇藤 | 物理学 | | 5 | 9 | 宇藤 | 化学 | +----+------------+--------+-------------+ 5 rows in set (0.00 sec) |
ON DELETE CASCADE
参照先、親テーブルのid=9のレコードを削除する。RESTRICTではエラーになったが、CASCADEの場合は削除される。
|
1 2 |
mysql> DELETE FROM students WHERE id = 9; Query OK, 1 row affected (0.00 sec) |
student_coursesのstudent_id=9のレコードも削除されている。
|
1 2 3 4 5 6 7 8 |
+----+------------+--------+-------------+ | id | student_id | name | course_name | +----+------------+--------+-------------+ | 1 | 1 | 安藤 | 数学 | | 2 | 1 | 安藤 | 物理学 | | 3 | 2 | 伊藤 | 数学 | +----+------------+--------+-------------+ 3 rows in set (0.01 sec) |
外部キー制約の追加・削除
外部キーの追加は、ALTER TABLE ... ADD FOREIGIN KEYで行う。被参照キーにインデックスが設定されている必要がある。
|
1 2 3 |
ALTER TABLE 参照テーブル ADD FOREIGN KEY (参照キー) REFERENCES 被参照テーブル(被参照キー); |
外部キーの削除はALTER TABLE ... DROP FOREIGN KEYで行う。制約名はSHOW CREATE TABLEで確認できる。
|
1 |
ALTER TABLE 参照テーブル DROP FOREIGN KEY 制約名; |