外部キーについて（MySQL編）

Laravel の開発に限らず、動的サイトを作成するなら、DBのテーブル間のリレーションを考えることは必須です。

そこで頻繁に出てくるキーワードが「外部キー制約」という仕組みです。

参照整合性を保つための便利な仕組みなのですが、開発初期のときは邪魔だったりします。
（※外部キーが多すぎてテーブルが消せなかったり。。）

外部キーについてはあまり意識して使ったことがなかったので、この問題にハマってしまいました。

今回はMySQLにおける外部キー（FOREIGN KEY）に関してのエントリーです。

※なお、今回の記事ではMySQLコマンドを多用します。コマンドがあやしい方は以下をご参考ください。

MySQLコマンド（早見表）

 

外部キー（FOREIGN KEY）とは

外部キーとは他のテーブルのデータに参照（依存）するようにカラムにつける制約のことです。

外部キーを設定すると、関連するテーブル間の整合性をデータベースに保証させることができます。

具体的には外部キーを設定すると以下のような制約を設定できます。

例）商品一覧テーブル（親テーブル）と売上テーブル（子テーブル）

・商品テーブルに存在しない商品名は、売上テーブルに登録させない
・売上テーブルに登録のある値は、商品テーブルから削除させない

 

外部キーの作成の仕方

書式

親テーブル

まず、外部キーの参照先となる親テーブルを作成します。

CREATE TABLE 親テーブル名（親カラム名 データ型）ENGINE=InnoDB;

 

 
子テーブル

CREATE TABLE 子テーブル名(子カラム名 データ型,
  FOREIGN KEY (子カラム名)  -- 外部キーの設定
  REFERENCES 親テーブル名(親カラム名)
) ENGINE=InnoDB;

 

 

書式例

まず最初に親テーブルである「商品テーブル」を作成します。

次に「売上テーブル」の商品名（name）に、外部キーを設定します。

 
親テーブル

CREATE TABLE goods(
  id int,  -- ID
  name varchar(10),  -- 商品名
  index(name)  -- 商品名に index を設定
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- テストデータ挿入
INSERT INTO goods VALUES('1', '消しゴム');
INSERT INTO goods VALUES('2', '鉛筆');
INSERT INTO goods VALUES('3', 'ボールペン');

 

 
子テーブル

子テーブルに下記のように外部キーを設定します。

外部キーを設定すると、子テーブルの「name」カラムには親テーブルの「name」カラムに格納されている値しか格納できません。

（※「消しゴム」「鉛筆」「ボールペン」しか値をいれることができません。）

CREATE TABLE sales(
  id int,  -- ID
  name varchar(10),  -- 商品名
  created_date date,  -- 作成日
  index(name), -- 商品名に index を設定
  FOREIGN KEY(name)  -- 外部キーを設定
  REFERENCES goods(name)  -- 商品テーブル.商品名
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- テストデータ
INSERT INTO sales VALUES('1', '消しゴム' , '18-12-05');
INSERT INTO sales VALUES('2', '消しゴム' , '18-12-06');
INSERT INTO sales VALUES('3', 'ボールペン' , '18-12-15');

 

検証作業

親テーブルに存在しない値をいれる

親テーブル（商品テーブル）に存在しない値を子テーブル（売上げテーブル）にいれてみます。

INSERT INTO sales VALUES('3', '筆' , '18-12-07');

 
実行結果

このように親テーブルに存在しない値を持つデータを追加しようとするとエラーが発生します。

 
親テーブルのカラムを削除

売上げテーブルにある「ボールペン」
親テーブル側の「ボールペン」を削除してみます。

DELETE FROM goods WHERE name='ボールペン';

 
実行結果

このように売上テーブルに登録のある値は、商品テーブルから削除できません。

 

外部キーの確認

MySQLで外部キーの確認をする場合は以下のコマンドを実行します。

DESC table_name;

 
実行結果

 

なお、参照先を調べるには show create tableで調べることができます。

SHOW CREATE TABLE sales;

 

 

外部キーの削除

手順

１）外部キー制約の確認
２）外部キー制約の削除

 

１）外部キー制約の確認

SHOW CREATE TABLE を使って、内部で生成された 外部キーID を検出します。

SHOW CREATE TABLE sales;

上記を実行すると以下のように結果が返ってきます。

この場合だと sales_ibfk_1 が外部キーIDになります。

 

２）外部キー制約の削除

以下のように外部キーIDを指定してやると外部キーが消せます。

-- 書式
ALTER TABLE テーブル名 DROP FOREIGN KEY 外部キーID;

ALTER TABLE sales DROP FOREIGN KEY sales_ibfk_1;

これで外部キー制約が外れます。

 

再び外部キーをつける

このとき整合性のないデータが入っているとエラーが出るので注意。

-- 書式
ALTER TABLE テーブル名 ADD CONSTRAINT 外部キーID FOREIGN KEY カラム名 REFERENCES 親テーブル（カラム名）ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE sales ADD CONSTRAINT sales_ibfk_1 FOREIGN KEY (name) REFERENCES goods(name) ON DELETE CASCADE ON UPDATE CASCADE;

 
以上です。

 

 

-周辺知識