外部キー制約(FOREIGN KEY)

Tag:

外部キー制約の設定方法の紹介と動作確認を行います。

外部キー制約とは

外部キー制約を設定することで、どのような制限が発生するのか、下記テーブルを例に説明します。
mysql_fk
「伝票テーブル」の商品コードと顧客コードが外部キーとなっています。

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

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

外部キー制約の実現方法

外部キー制約を実現する方法は2つあります。
・データベース上で外部キー制約を設定
・アプリケーションで外部キー制約の制限のもと動作するように開発

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

アプリケーションだけでデータの整合性を維持しようとすると、後々、
・何かしらのトラブルが発生する可能性が高まること
・データの整合性が維持されているかを確認するための作業が必要になること
を考えなければなりません。

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

MySQLで外部キー制約を設定

mysql_fk
さっそく、上記ER図が示す通りにテーブルを作成します。

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;

21~22行目で外部キー制約を設定しています(この設定だと問題があります。詳しくは後述)。外部キー制約を設定するには下記条件を満たしている必要があります。

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

動作確認|準備

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

INSERT INTO `商品` (`商品コード`, `商品名`, `単価`) VALUES ('1', 'ナス', '150'), ('2', 'トマト', '100'), ('3', 'ジャガイモ', '50'), ('4', 'カボチャ', '200'); 
INSERT INTO `顧客` (`顧客コード`, `顧客名`, `年齢`) VALUES ('1', '鈴木', '34'), ('2', '山本', '29'), ('3', '元木', '42'); 

商品テーブルには、以下のようにデータが挿入されました。
mysql_fk3png

顧客テーブルには、以下のようにデータが挿入されました。
mysql_fk4png

動作確認|データ挿入のエラー

伝票テーブルにデータを挿入してみます。

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

上記クエリはエラーとなります。商品テーブルに存在しない商品コードを挿入しようとしているためです。

動作確認|データ更新のエラー(同時更新の必要性)

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

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

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

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

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

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

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

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

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

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;

8,10行目にて「ON UPDATE CASCADE」と宣言しています。

「ON UPDATE CASCADE」と宣言されていれば、先ほどエラーとなった下記SQLを実行することができます。

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

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

動作確認|データ削除エラー

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

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

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

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

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

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

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

対して、参照関係の場合はどうでしょうか。参照しないという選択肢もありえます。つまり、「参照先テーブルのレコードが削除された場合、参照元テーブルから参照しなくする」といった動作です。この動作を実現するには、テーブル作成時に次のように外部キー制約を設定します。

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;

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

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

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

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

スポンサーリンク