MySQL で外部キー制約をオフにする
今日は、MySQL Workbench で FOREIGN_KEY_CHECKS を使用して、MySQL の外部キー制約を一時的に無効にする方法を学びます。
MySQL で外部キー制約をオフにする
外部キーを一時的にオフにする状況はさまざまです。 たとえば、データを parent テーブルと child テーブルに任意の順序でロードします。
その場合、FOREIGN_KEY_CHECKS を使用して、MySQL Server で 外部キー制約 をオフにすることができます。 それを学ぶために、2つのテーブルを作成し、最初にデータを入力してみましょう。
コード例:
# create a `student` table
CREATE TABLE student(
student_id INT NOT NULL PRIMARY KEY,
student_name VARCHAR(255) NOT NULL
);
# create a `course` table
CREATE TABLE course(
course_id INT NOT NULL PRIMARY KEY,
course_name VARCHAR(255),
student_id INT,
FOREIGN KEY(student_id)
REFERENCES student(student_id)
);
コード例:
# insert data into the `student` table
INSERT INTO student(student_id, student_name)
VALUES
(1, 'Maryam Taymor'),
(2, 'Mehvish Ashiq'),
(3, 'James Daniel'),
(4, 'Rahul Agarwal');
# insert data into the `course` table
INSERT INTO course(course_id, course_name, student_id)
VALUES
(1, 'Java Programming', 4),
(2, 'Data Science', 3),
(3, 'Computer Vision', 2),
(4, 'Python Programming', 1);
SELECT コマンドを使用して、両方のテーブルの現在のデータを表示します。
SELECT * FROM student;
SELECT * FROM course;
出力 (student テーブル用):
+------------+---------------+
| student_id | student_name |
+------------+---------------+
| 1 | Maryam Taymor |
| 2 | Mehvish Ashiq |
| 3 | James Daniel |
| 4 | Rahul Agarwal |
+------------+---------------+
4 rows in set (0.00 sec)
出力 (course テーブルの場合):
+-----------+----------------------+------------+
| course_id | course_name | student_id |
+-----------+----------------------+------------+
| 1 | Java Programming | 4 |
| 2 | Data Science | 3 |
| 3 | Computer Vision | 2 |
| 4 | Python Programming | 1 |
+-----------+----------------------+------------+
4 rows in set (0.00 sec)
FOREIGN_KEY_CHECKS を設定して MySQL の外部キーをオフにする
Javaプログラミングに登録したい別の学生がいるとしましょう。 今回は、最初にそのレコードを子テーブル (course テーブル) に挿入します。
コード例:
INSERT INTO course(course_id,course_name, student_id)
VALUES
(5, 'Java Programming', 5);
course テーブルにレコードを挿入するクエリを実行するとすぐに、次のエラーが生成されます。
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ms23`.`course`, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`))
course テーブルに外部キーがあるために発生します。 したがって、次のように外部キー チェックを無効にできます。
SET foreign_key_checks = 0;
次に、それを course テーブルに再度挿入します。
INSERT INTO course(course_id,course_name, student_id)
VALUES
(5, 'Java Programming', 5);
今度は、レコードが正常に挿入されました。 SELECT コマンドを使用して、挿入を確認します。
SELECT * from course;
出力 (course テーブルの場合):
+-----------+----------------------+------------+
| course_id | course_name | student_id |
+-----------+----------------------+------------+
| 1 | Java Programming | 4 |
| 2 | Data Science | 3 |
| 3 | Computer Vision | 2 |
| 4 | Python Programming | 1 |
| 5 | Java Programming | 5 |
+-----------+----------------------+------------+
5 rows in set (0.00 sec)
FOREIGN_KEY_CHECKS の値を 1 に設定して、外部キー制約チェックを再度有効にすることを忘れないでください。
SET foreign_key_checks = 1;
phpMyAdmin でも同じコマンドを使用できますが、次のスクリーンショットで強調表示されているオプションを必ず UNCHECK してください。
FOREIGN_KEY_CHECKS の値を 1 に戻すと、外部キー チェックをオフにした後に挿入した現在のデータの検証がトリガーされないことを思い出してください。
データベースへの新しい更新と追加のみをチェックします。 FOREIGN_KEY_CHECKS を使用して、外部キー制約をグローバルに無効にすることもできます。
コード例:
# turn off foreign key constraints globally
SET GLOBAL FOREIGN_KEY_CHECKS=0;
# turn on foreign key constraints globally
SET GLOBAL FOREIGN_KEY_CHECKS=1;
