周辺知識

外部キーについて(MySQL編)

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

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

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

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

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

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

 

外部キー(FOREIGN KEY)とは

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

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

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

例)商品一覧テーブル(親テーブル)と売上テーブル(子テーブル)

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

 

外部キーの作成の仕方

書式

親テーブル

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


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

 

ポイント

作成するテーブルは InnoDB(イノデービー)である必要があります。
昔のデフォルトのエンジン MyISAM(マイアイサム)は外部キー制約を持っていません。

 
子テーブル


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

 

ポイント

外部キーの設定は CREATE TABLE の最後に追加します。
また、対象のカラムにはインデックスが作成されている必要があります。

 

書式例

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

次に「売上テーブル」の商品名(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', 'ボールペン');

 

MySQLのindex

テーブル内のデータ量が多く、少量のレコードを検索する場合はカラムに index を設定します。WHERE句の条件や結合の条件として頻繁に利用するカラムなら設定すると検索スピードが上がります。

 
子テーブル

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

外部キーを設定すると、子テーブルの「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;

 
実行結果

 

ポイント

設定するとKey欄がMULとなります。

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


SHOW CREATE TABLE sales;

 

 

外部キーの削除

手順

1)外部キー制約の確認
2)外部キー制約の削除

 

1)外部キー制約の確認

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


SHOW CREATE TABLE sales;

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

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

 

2)外部キー制約の削除

以下のように外部キー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;

以上です。

本庄マサノリ

仕事でLaravelを使っています。気づいたことや新しい発見など情報を発信していきます。メールはこちら

 

-周辺知識