MySQLで外部キー制約の動作を確認する
モチベーション
ActiveRecordでアソシエーションの設定をする前に、MySQLの外部キー制約の設定方法の確認をしたい
準備
環境
mysql --version mysql Ver 14.14 Distrib 5.6.25, for osx10.10 (x86_64) using EditLine wrapper
実行するクエリ
CREATE TABLE authors(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE publishers(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE books(
id INT NOT NULL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author_id INT NOT NULL,
publisher_id INT NOT NULL,
FOREIGN KEY(author_id)
REFERENCES authors(id)
ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY(publisher_id)
REFERENCES publishers(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
INSERT authors(id, name) VALUES
(1, 'author1'),
(2, 'author2'),
(3, 'author3'),
(4, 'author4'),
(5, 'author5');
INSERT publishers (id, name) VALUES
(1, 'publisher1'),
(2, 'publisher2'),
(3, 'publisher3'),
(4, 'publisher4'),
(5, 'publisher5');
INSERT books(id ,title, author_id, publisher_id) VALUES
(1, 'book1', 1, 1),
(2, 'book2', 2, 2),
(3, 'book3', 3, 3),
(4, 'book4', 4, 4);
この時、小テーブルのにレコードを挿入する、
INSERT books(id ,title, author_id, publisher_id) VALUES
(1, 'book1', 1, 1),
(2, 'book2', 2, 2),
(3, 'book3', 3, 3),
(5, 'book4', 4, 4);を最後に実行しないと以下のエラーが出てレコードを挿入できない。
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`book`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`))各テーブルのレコード情報
mysql> SELECT * FROM authors; +----+---------+ | id | name | +----+---------+ | 1 | author1 | | 2 | author2 | | 3 | author3 | | 4 | author4 | | 5 | author5 | +----+---------+ mysql> SELECT * FROM publishers; +----+------------+ | id | name | +----+------------+ | 1 | publisher1 | | 2 | publisher2 | | 3 | publisher3 | | 4 | publisher4 | | 5 | publisher5 | +----+------------+ mysql> SELECT * FROM books; +----+-------+-----------+--------------+ | id | title | author_id | publisher_id | +----+-------+-----------+--------------+ | 1 | book1 | 1 | 1 | | 2 | book2 | 2 | 2 | | 3 | book3 | 3 | 3 | | 4 | book4 | 4 | 4 | +----+-------+-----------+--------------+
準備完了！
外部キー制約の動作確認
作成したテーブルやレコードを元に外部キー制約の動作を確認する。外部キー制約としてCASCADEとRESTRICT以外にもSET NULLやNO ACTIONもあるが今回は動作確認対象外とする。
UPDATE ON RESTRICTの動作確認
mysql> UPDATE authors SET id = 100 WHERE id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`book`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`))
続いて、books.author_id = 1のレコードを全て削除した後に親レコードを更新する。
mysql> DELETE FROM books WHERE author_id =1; Query OK, 2 rows affected (0.00 sec) mysql> UPDATE authors SET id = 100 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
authors.id = 1のレコードがid =100に更新されていることを確認する。
mysql> SELECT * from authors; +-----+---------+ | id | name | +-----+---------+ | 2 | author2 | | 3 | author3 | | 4 | author4 | | 5 | author5 | | 100 | author1 | +-----+---------+ mysql> SELECT * FROM books; +----+-------+-----------+--------------+ | id | title | author_id | publisher_id | +----+-------+-----------+--------------+ | 2 | book2 | 2 | 2 | | 3 | book3 | 3 | 3 | | 4 | book4 | 4 | 4 | +----+-------+-----------+--------------+
DELETE ON RESTRICTの動作確認
まずは、RESTRICTで親レコードを消せないことを確認する。
mysql> DELETE FROM authors WHERE id =2; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`book`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`))
続いて、books.authors_id = 2の子レコードを全て削除したと後に、親レコードを削除する。
mysql> DELETE FROM books WHERE author_id =2; Query OK, 2 rows affected (0.00 sec) mysql> DELETE FROM authors WHERE id =2; Query OK, 1 row affected (0.00 sec)
authors.id = 2の親レコードが削除されていることを結果を確認する。
mysql> SELECT * FROM authors; +-----+---------+ | id | name | +-----+---------+ | 3 | author3 | | 4 | author4 | | 5 | author5 | | 100 | author1 | +-----+---------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM books; +----+-------+-----------+--------------+ | id | title | author_id | publisher_id | +----+-------+-----------+--------------+ | 3 | book3 | 3 | 3 | | 4 | book4 | 4 | 4 | +----+-------+-----------+--------------+
UPDATE ON CASCADEの動作確認
CASCADEの場合はいきなり親要素の更新が出来る。
mysql> UPDATE publishers SET id = 300 WHERE id = 3;
結果を確認。
mysql> SELECT * FROM publishers; +-----+------------+ | id | name | +-----+------------+ | 1 | publisher1 | | 2 | publisher2 | | 4 | publisher4 | | 5 | publisher5 | | 300 | publisher3 | +-----+------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM books; +----+-------+-----------+--------------+ | id | title | author_id | publisher_id | +----+-------+-----------+--------------+ | 3 | book3 | 3 | 300 | | 4 | book4 | 4 | 4 | +----+-------+-----------+--------------+
子レコードの更新に伴い、親レコードも更新されている。
DELETE ON CASCADEの動作確認
DELETEも同様子レコードの削除に伴い、親レコードも削除される
DELETE FROM publishers WHERE id =4;
book.id = 4が同時に削除されているか、結果を確認する。
mysql> SELECT * FROM publishers; +-----+------------+ | id | name | +-----+------------+ | 1 | publisher1 | | 2 | publisher2 | | 5 | publisher5 | | 300 | publisher3 | +-----+------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM books; +----+-------+-----------+--------------+ | id | title | author_id | publisher_id | +----+-------+-----------+--------------+ | 3 | book3 | 3 | 300 | +----+-------+-----------+--------------+
