外部キー制約の設定と動作確認

2022/11/12

「MySQLで外部キー制約を設定する方法」「外部キー制約のオプション設定(RESTRICTCASCADESET NULLNO ACTION)による動作の違い」について解説します。

外部キー制約の実現方法

外部キー制約を実現する方法は2つあります。

データベース上で外部キー制約を設定
アプリケーションで外部キー制約の制限のもと動作するように開発

MySQLのデータベースエンジンであるMyISAMは、外部キー制約をサポートしていません。そのため、アプリケーションで外部キー制約を考慮する必要がありました。しかし、機能拡張などしていくうちに、制限のことが忘れ去られることが多く、データの整合性が崩れるといったことが起こりえます。

アプリケーションだけでデータの整合性を維持しようとすると、後々、

何かしらのトラブルが発生する可能性が高まること
データの整合性が維持されているかを確認するための作業が必要になること

を考えなければなりません。

MySQLでもInnoDBでは、外部キー制約をサポートしています。参照整合性を確実に維持するために、データベース上で外部キー制約を設定することをおすすめします。

MySQLで外部キー制約を設定

例題テーブル

MySQLで外部キー制約を設定してみます。下記テーブルを例に説明します。

「伝票テーブル」の商品コードと顧客コードが外部キーとなっています。

外部キー制約が設定されているため、
「伝票テーブル」の商品コードに設定される値は、
「商品テーブル」に存在する商品コードのみに制限されます。

同様に、
「伝票テーブル」の顧客コードに設定される値は、
「顧客テーブル」に存在する顧客コードのみに制限されます。

テーブル作成

以下クエリでテーブルを作成します。

CREATE TABLE `test`.`商品` (
 `商品コード` INT NOT NULL ,
 `商品名` VARCHAR(100) NOT NULL ,
 `単価` INT NOT NULL ,
  PRIMARY KEY (`商品コード`)
) ENGINE = InnoDB;
 
CREATE TABLE `test`.`顧客` (
 `顧客コード` INT NOT NULL ,
 `顧客名` VARCHAR(100) NOT NULL ,
 `年齢` INT NOT NULL ,
  PRIMARY KEY (`顧客コード`)
) ENGINE = InnoDB;
 
CREATE TABLE `test`.`伝票` (
 `伝票番号` INT NOT NULL ,
 `商品コード` INT NOT NULL ,
 `数量` INT NOT NULL ,
 `顧客コード` INT NOT NULL ,
 PRIMARY KEY (`伝票番号`, `商品コード`) ,
 FOREIGN KEY (`商品コード`) REFERENCES 商品(`商品コード`) ,
 FOREIGN KEY (`顧客コード`) REFERENCES 顧客(`顧客コード`)
) ENGINE = InnoDB;

FOREIGN KEY で外部キーを設定しています。
FOREIGN KEY では以下オプションを設定できるのですが、ここでは設定していません。

ON UPDATE reference_option
ON DELETE reference_option

設定しないことで発生する問題については、後述します。

外部キー制約の設定条件

外部キー制約を設定するには下記条件を満たしている必要があります。

InnoDBを利用していること
「外部キー制約を設定するカラム」と「外部キーが参照するカラム」の型が一致していること
「外部キーが参照するカラム」にインデックスがはられていること

動作確認

確認レコード挿入

まず、外部キーが参照しているテーブルにレコードを挿入します。

INSERT INTO `商品` (`商品コード`, `商品名`, `単価`) 
VALUES ('1', 'ナス', '150'), ('2', 'トマト', '100'), ('3', 'ジャガイモ', '50'), ('4', 'カボチャ', '200'); 

INSERT INTO `顧客` (`顧客コード`, `顧客名`, `年齢`) 
VALUES ('1', '鈴木', '34'), ('2', '山本', '29'), ('3', '元木', '42');

以下のようにレコードが挿入されました。

mysql> SELECT * FROM `商品`;
+-----------------+-----------------+--------+
| 商品コード      | 商品名          | 単価   |
+-----------------+-----------------+--------+
|               1 | ナス            |    150 |
|               2 | トマト          |    100 |
|               3 | ジャガイモ      |     50 |
|               4 | カボチャ        |    200 |
+-----------------+-----------------+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM `顧客`;
+-----------------+-----------+--------+
| 顧客コード      | 顧客名    | 年齢   |
+-----------------+-----------+--------+
|               1 | 鈴木      |     34 |
|               2 | 山本      |     29 |
|               3 | 元木      |     42 |
+-----------------+-----------+--------+
3 rows in set (0.00 sec)

レコード挿入のエラー

伝票テーブルにレコードを挿入してみます。

mysql> INSERT INTO `伝票` (`伝票番号`, `商品コード`, `数量`, `顧客コード`) 
    -> VALUES (1, 100, 2, 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`伝票`, CONSTRAINT `伝票_ibfk_1` FOREIGN KEY (`商品コード`) REFERENCES `商品` (`商品コード`))

Cannot add or update a child row というエラーになりました。
商品テーブルに存在しない商品コードを挿入しようとしているためです。

レコード更新のエラー
( 同時更新の必要性 )

準備として、伝票テーブルに次のクエリを実行してレコードを挿入します。

INSERT INTO `伝票` (`伝票番号`, `商品コード`, `数量`, `顧客コード`)
VALUES ('1', '2', '2', '2'), 
       ('1', '4', '1', '2'), 
       ('2', '3', '4', '3'), 
       ('3', '1', '2', '1'), 
       ('3', '2', '1', '1');

現在の状態は以下の通りです。

制約によるエラー

mysql> UPDATE `商品` SET `商品コード` = 5 WHERE `商品`.`商品コード` = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`伝票`, CONSTRAINT `伝票_ibfk_1` FOREIGN KEY (`商品コード`) REFERENCES `商品コード`))

伝票テーブルに商品コードが1であるレコードが存在するため、この更新を許すと外部キーの参照する値がなくなってしまうのでエラーになりました。

mysql> UPDATE `伝票` SET `商品コード` = 5 WHERE `伝票`.`商品コード` = 1;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`伝票`, CONSTRAINT `伝票_ibfk_1` FOREIGN KEY (`商品コード`) REFERENCES `商品` (`商品コード`))

商品テーブルに商品コードが5であるレコードが存在しないためエラーとなりました。

同時更新できるようにする

更新するためには、「伝票テーブルの商品コード」と「商品テーブルの商品コード」を同時に更新する必要があります。同時更新を実現するには、テーブル作成時に次のように外部キー制約を設定します。

CREATE TABLE `test`.`伝票` (
 `伝票番号` INT NOT NULL ,
 `商品コード` INT NOT NULL ,
 `数量` INT NOT NULL ,
 `顧客コード` INT NOT NULL ,
 PRIMARY KEY (`伝票番号`, `商品コード`) ,
 FOREIGN KEY (`商品コード`) REFERENCES 商品(`商品コード`)
  ON UPDATE CASCADE ,
 FOREIGN KEY (`顧客コード`) REFERENCES 顧客(`顧客コード`)
  ON UPDATE CASCADE
) ENGINE = InnoDB;

FOREIGN KEY のオプションで ON UPDATE CASCADE と宣言しています。
ON UPDATE CASCADE と宣言されていれば、先ほどエラーとなった下記SQLを実行することができます。

UPDATE `商品` SET `商品コード` = 5 WHERE `商品`.`商品コード` = 1

伝票テーブルの対応する商品コードも同時に自動更新されます。

レコード削除エラー

下記SQLはエラーとなります。

mysql> DELETE FROM `顧客` WHERE `顧客`.`顧客コード` = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`伝票`, CONSTRAINT `伝票_ibfk_2` FOREIGN KEY (`顧客コード`) REFERENCES `顧コード`))

伝票テーブルに顧客コードが1であるレコードが存在するためです。

ここでもう一度、テーブルの関係を見てみます。

伝票テーブルの複合主キーの一部である商品コードが外部キーとなって商品テーブルを参照しています。このとき、商品テーブルと伝票テーブルは親子関係であるといえます。

伝票テーブルの主キー以外のカラムである顧客コードが外部キーとなって顧客テーブルを参照しています。このとき、顧客テーブルと伝票テーブルは参照関係であるといえます。

親子関係の場合、親がいなければ子は生まれません。親と子の関係は必ず存在します。よって、子テーブルの商品コードに存在する値は、必ず、親テーブルの商品コードに存在しなければいけません。

対して、参照関係の場合はどうでしょうか。参照しないという選択肢もありえます。つまり、「参照先テーブルのレコードが削除された場合、参照元テーブルから参照しなくする」といった動作です。

参照しなくする設定

「参照先テーブルのレコードが削除された場合、参照元テーブルから参照しなくする」といった動作を実現するには、テーブル作成時に次のように外部キー制約を設定します。

CREATE TABLE `test`.`伝票` (
 `伝票番号` INT NOT NULL ,
 `商品コード` INT NOT NULL ,
 `数量` INT NOT NULL ,
 `顧客コード` INT NULL,
 PRIMARY KEY (`伝票番号`, `商品コード`) ,
 FOREIGN KEY (`商品コード`) REFERENCES 商品(`商品コード`)
  ON UPDATE CASCADE ,
 FOREIGN KEY (`顧客コード`) REFERENCES 顧客(`顧客コード`)
  ON UPDATE CASCADE
  ON DELETE SET NULL
) ENGINE = InnoDB;

ON DELETE SET NULL と宣言することで参照先が削除されたとき、NULLが設定されるようにしています。
また、NULLが入力できるように、 顧客コード の値として NULLを許可 するように変更しています。

これで、先ほどエラーとなった下記SQLを実行することができます。

DELETE FROM `顧客` WHERE `顧客`.`顧客コード` = 1;

伝票テーブルの対応する顧客コードもNULLが自動設定されます。

FOREIGN KEYのオプション

ここで、FOREIGN KEYのオプションについて再確認します。
以下オプションを指定できます。

ON UPDATE reference_option
ON DELETE reference_option

reference_optionに設定できる値は以下の通りです。

reference_option	動作
RESTRICT	エラーになる(デフォルト)
CASCADE	参照先の変更と同じ変更が参照元で行われる
SET NULL	参照元でNULLになる
NO ACTION	RESTRICTと同じ