Pocket

モチベーション

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 |
+----+-------+-----------+--------------+
Pocket

Share Your Thought

CAPTCHA