04_主キーに存在する値だけを外部キーに挿入

概要

• 主キーに存在する値だけを外部キーに挿入する方法についてまとめる。

外部キー制約

概要

• あるテーブル(親)の主キーを参照するテーブル(子)を作成し，
  親テーブルの主キーに存在する値のみを，子テーブルに格納するような制約のこと。

文法

CREATE TABLE テーブル名 (
列名 データ型,
…
FOREIGN KEY (外部キーにする列名)
REFERENCES 参照先テーブル名(主キー)
);

サンプル

事前準備

参照されるテーブルの作成

CREATE TABLE divisions(
		id INT PRIMARY KEY,
		name VARCHAR(16)
		);

• 実行結果

MariaDB [dekirusample2]> CREATE TABLE divisions(id INT PRIMARY KEY,name VARCHAR(16));
Query OK, 0 rows affected (0.12 sec)

データの挿入

INSERT INTO divisions VALUES 
(0, '営業部'),
(1, '総務部');

• 実行結果

MariaDB [dekirusample2]> INSERT INTO divisions VALUES (0, '営業部'),(1, '総務部');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

外部キー制約を指定してテーブルを作成

	CREATE TABLE employees(
			name VARCHAR(16),
			division_id INT,
			FOREIGN KEY(division_id) REFERENCES divisions(id)
			);

• 実行結果

MariaDB [dekirusample2]> CREATE TABLE employees(name VARCHAR(16),division_id INT,FOREIGN KEY(division_id) REFERENCES divisions(id));
Query OK, 0 rows affected (0.07 sec)

親テーブル(divisions)にある値を挿入

INSERT INTO employees VALUES 
('太宰治',0),
('大庭葉蔵',1);

• 実行結果
  • 挿入が成功する。

MariaDB [dekirusample2]> INSERT INTO employees VALUES ('太宰治',0),('大庭葉蔵',1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

親テーブル(divisions)にない値を挿入

INSERT INTO employees VALUES 
('小林多喜二',2);

• 実行結果
  • 挿入が失敗する。

MariaDB [dekirusample2]> INSERT INTO employees VALUES ('小林多喜二',2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dekirusample2`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY
(`division_id`) REFERENCES `divisions` (`id`))