親テーブルの更新/削除の時の挙動

前のページで確認した通り、デフォルトでは外部キー制約を設定している親テーブルの該当カラムの値を更新したり、データそのものを削除しようとするとエラーが発生します。ここでは親テーブルを更新したりデータを削除したりした場合の挙動の設定を行います。

更新時の挙動についてはON UPDATE句を使います。

CREATE TABLE 子テーブル名(子カラム名 データ型,
  FOREIGN KEY (子カラム名)
  REFERENCES 親テーブル名(親カラム名)
  ON UPDATE reference_option
) ENGINE=InnoDB;

削除時の挙動についてはON DELETE句を使います。

CREATE TABLE 子テーブル名(子カラム名 データ型,
  FOREIGN KEY (子カラム名)
  REFERENCES 親テーブル名(親カラム名)
  ON DELETE reference_option
) ENGINE=InnoDB;

※ 同時に指定することもできます。その場合は「ON DELETE reference_option ON UPDATE reference_option」と続けて記述して下さい。

ON DELETE句とON UPDATE句に対しては次の値を指定することができます。

RESTRICT
CASCADE
SET NULL
NO ACTION

それでは一つ一つ確認していきます。なおいずれの場合でも親テーブルは同じであり、次のようなテーブルを作成しデータをいくつか追加してあります。

親テーブル：

mysql> create table goods(
    -> id int, name varchar(10), index(name)
    -> ) engine=InnoDB;

p12-1

RESTRICT

ON DELETE句やON UPDATE句にRESTRICTを指定した場合は、親テーブルに対して更新や削除を行うとエラーが発生します。RESTRICTはN DELETE句やON UPDATE句を明示的に指定しなかった場合に設定される値です。

では次のような子テーブルを作成します。今回はON UPDATE句にNO RESTRICTを指定しています。

mysql> create table sales1(
    -> id int, name varchar(10), d date, index(name),
    -> foreign key(name) references goods(name) on update restrict
    -> ) engine=InnoDB;

p12-2

子テーブルにデータを追加します。

p12-3

では親テーブルの「name」カラムの値を更新してみます。

p12-4

上記のようにエラーが発生し更新は行えません。

今回は更新を行いましたが親テーブルのデータを削除しようとした場合もエラーとなります。ではいったん親テーブルを参照しているテーブルを削除するために「sales1」テーブルを削除して下さい。

NO ACTION

ON DELETE句やON UPDATE句にNO ACTIONを指定した場合は、親テーブルに対して更新や削除を行うとエラーが発生します。MySQLにおいてはNO ACTIONとRESTRICTは同じ挙動となります。

では次のような子テーブルを作成します。今回はON DELETE句にNO ACTIONを指定しています。

mysql> create table sales2(
    -> id int, name varchar(10), d date, index(name),
    -> foreign key(name) references goods(name) on delete no action
    -> ) engine=InnoDB;

p12-5

子テーブルにデータを追加します。

p12-6

では親テーブルのデータを削除してみます。

p12-7

上記のようにエラーが発生し削除は行えません。

今回は親データを削除しようとしましたが更新しようとした場合もエラーとなります。ではいったん親テーブルを参照しているテーブルを削除するために「sales2」テーブルを削除して下さい。

CASCADE

ON DELETE句やON UPDATE句にCASCADEを指定した場合は、親テーブルに対して更新を行うと子テーブルで同じ値を持つカラムの値も合わせて更新されます。また親テーブルに対して削除を行うと子テーブルで同じ値を持つデータが削除されます。

では次のような子テーブルを作成します。今回はON UPDATE句及びON DELETE句にCASCADEを指定しています。

mysql> create table sales3(
    -> id int, name varchar(10), d date, index(name),
    -> foreign key(name) references goods(name)
    -> on update cascade on delete cascade
    -> ) engine=InnoDB;

p12-8

子テーブルにデータを追加します。

p12-9

では親テーブルの「name」カラムの値を更新してみます。

p12-10

今回は親テーブルの更新に成功しました。では親テーブル及び子テーブルの内容を確認してみます。

p12-11

親テーブルで更新したデータと同じ値が格納されていた子テーブルのデータも同時に更新されていることが確認できます。

続いて親テーブルのデータを削除してみます。

p12-12

親テーブルのデータの削除に成功しました。では親テーブル及び子テーブルの内容を確認してみます。

p12-13

親テーブルで削除したデータと同じ値が格納されていた子テーブルのデータも同時に削除されていることが確認できます。

このようにCASCADEを指定した場合には、親テーブルの更新に合わせて子テーブルのデータも更新され、親テーブルのデータの削除に合わせて子テーブルのデータも削除されます。ではいったん親テーブルを参照しているテーブルを削除するために「sales3」テーブルを削除して下さい。また親テーブルもデータが一部変更されたので元の状態に戻してあります。

SET NULL

ON DELETE句やON UPDATE句にSET NULLを指定した場合は、親テーブルに対して更新を行うと子テーブルで同じ値を持つカラムの値がNULLに更新されます。また親テーブルに対して削除を行うと子テーブルで同じ値を持つデータのカラムの値がNULLに更新されます。

では次のような子テーブルを作成します。今回はON UPDATE句及びON DELETE句にET NULLを指定しています。

mysql> create table sales4(
    -> id int, name varchar(10), d date, index(name),
    -> foreign key(name) references goods(name)
    -> on update set null on delete set null
    -> ) engine=InnoDB;

p12-14