2022-04-30 19:05:36

mysql 外部キー制約を観察する（ ON DELETE RESTRICT ）

準備

イメージを取得

docker pull mysql

コンテナ起動

docker run --name mysql_study_references -it --env MYSQL_ROOT_PASSWORD=password -d mysql

mysql に接続

mysql -h localhost -u root -ppassword

バージョン情報

mysql> select version(), current_date;
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 8.0.29    | 2022-04-30   |
+-----------+--------------+

データベース作成

mysql> create database learning character set utf8mb4;

データベース選択

mysql> use learning

参照制約付きカラムを含めたテーブル作成

CREATE TABLE parent (
    parent_id INT NOT NULL AUTO_INCREMENT,
    parent_name varchar(255),
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    child_id INT NOT NULL AUTO_INCREMENT,
    parent_id INT NOT NULL,
    PRIMARY KEY (child_id),
    INDEX parent_index (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(parent_id)
        ON DELETE RESTRICT
) ENGINE=INNODB;

データインサート

insert into parent (parent_name) values ('sample1'), ('sample2'), ('sample3'), ('sample4'), ('sample5');

insert into child (parent_id) values (1), (2), (3), (2), (3), (3), (1);

観察

child テーブルに parent テーブルに存在しないレコードをインサートする

mysql> insert into child (parent_id) values (6);

結果

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learning`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`) ON DELETE RESTRICT)

parent テーブルに存在しないデータを child テーブルにインサートすると、確かにエラーになることが確認できます。

parent テーブルのレコードを削除してみる

mysql> DELETE FROM parent where parent_id = 1;

結果

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`learning`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`) ON DELETE RESTRICT)

child テーブルにインサートすると、確かにエラーになることが確認できます。

child テーブルから参照されていない parent テーブルのレコードを削除してみる

新しく parent テーブルにレコードをインサートします。このレコードを参照する child テーブルのレコードは作成しません。

mysql> insert into parent (parent_name) values ('sample6');

インサートしたレコードを削除してみる。

mysql> DELETE FROM parent where parent_id = 6;

結果

Query OK, 1 row affected (0.01 sec)

child テーブルから参照されていないので削除できます。

トランケートするとき

TRUNCATE TABLE は外部キー関係を持つ InnoDB テーブルに対して実行することは出来ません。ので、一時的に外部キー制約を解除します。MyISAM の場合はそもそも外部キーをサポートしていないそうです。

mysql> set foreign_key_checks = 0;
mysql> truncate table parent;
mysql> truncate table child;
mysql> set foreign_key_checks = 1;

参考

https://hub.docker.com/_/mysql https://docs.docker.com/engine/reference/commandline/run/ https://dev.mysql.com/doc/refman/5.6/ja/create-table-foreign-keys.html https://dev.mysql.com/doc/refman/5.6/ja/truncate-table.html https://dev.mysql.com/doc/refman/5.6/ja/myisam-storage-engine.html https://qiita.com/pugiemonn/items/b17288494e4b627f4475

終わり

終わりです。じっくり観察するのはこれが初めてな気がします。今回使用した mysql のバージョンは8系ですが、参考にした公式サイトのバージョンが5.6なのは仕様です。