mysqldump したデータを取り込んだ場合の外部キー制約に関する検証のメモ

mysqldump したデータを取り込んだ場合の外部キー制約に関する検証のメモ。

外部キー制約の関係がある 2つのテーブルを条件指定つきで mysqldump で出力して、
その結果をロードした場合に、外部キー制約が効いているかを検証してみました。

結果としては、外部キー制約が効いていないレコードが登録された状態になります。

まず、以下の2つのテーブルを定義します。
constraint1 テーブルには id だけを登録します。
constraint2 テーブルには、constraint1 テーブルに id が登録されている id に対してのみ、
val を登録します。

create table constraint1 (
       id    int not null,
       primary key(id)
) ENGINE=InnoDB;

create table constraint2 (
       id    int not null,
       val   varchar(20) not null,

       primary key(id),
       foreign key (id)
               references constraint1(id)
               on delete cascade
) ENGINE=InnoDB;

insert into constraint1 set id = 1;
insert into constraint1 set id = 2;
insert into constraint1 set id = 3;
insert into constraint1 set id = 4;
insert into constraint1 set id = 5;

insert into constraint2 set id = 1, val = "val1";
insert into constraint2 set id = 2, val = "val2";
insert into constraint2 set id = 3, val = "val3";
insert into constraint2 set id = 4, val = "val4";
insert into constraint2 set id = 5, val = "val5";

各テーブルには以下のようにレコードが登録されています。

mysql> select * from constraint1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> select * from constraint2;
+----+------+
| id | val  |
+----+------+
|  1 | val1 |
|  2 | val2 |
|  3 | val3 |
|  4 | val4 |
|  5 | val5 |
+----+------+
5 rows in set (0.00 sec)

ここで、constraint1 のテーブルから id=5 のレコードを削除すると、
constraint2 のテーブルからも id=5 のレコードが削除されます。

mysql> delete from constraint1 where id = 5;
Query OK, 1 row affected (0.01 sec)

mysql> select * from constraint1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> select * from constraint2;
+----+------+
| id | val  |
+----+------+
|  1 | val1 |
|  2 | val2 |
|  3 | val3 |
|  4 | val4 |
+----+------+
4 rows in set (0.00 sec)

この状態で以下の mysqldump コマンドでテーブルを dump します。

mysqldump -u{id} -p{password} {db} constraint1 -w 'id <= 2' > dump_constraint1_2.sql
mysqldump -u{id} -p{password} {db} constraint2

constraint1 は id=1, 2 のレコードしか抽出しません。
一方、constraint2 は id=3, 4 のレコードも抽出します。
これらのデータを取り込んだ際に、constraint2 で id=3,4 のレコードが登録されるかを確認します。

cat dump_constraint1_2.sql | mysql -u{id} -p{password} {db}
cat dump_constraint2.sql | mysql -u{id} -p{password} {db}

結果は以下の通り。

mysql> select * from constraint1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> select * from constraint2;
+----+------+
| id | val  |
+----+------+
|  1 | val1 |
|  2 | val2 |
|  3 | val3 |
|  4 | val4 |
+----+------+
4 rows in set (0.00 sec)

constraint1 に登録されていない id のレコードが、constraint2 にも登録された状態になります。