【MySQL】FOREIGN KEY REFERENCES制約 - 外部キー(他テーブルの値)
MySQLのFOREIGN KEY制約について解説します。
検証環境
外部キー
外部キーは“別カラムを参照するカラム(キー)”です。
任意のカラムに設定でき、参照先のカラムに存在する値のみ格納できます。
FOREIGN KEY制約
FOREIGN KEY制約は“外部キーを設定する制約”です。
外部キー制約によって、参照先カラムに存在する値のみ保持することができます。
また、参照データを変更・削除した場合の連動した処理も設定可能です。
定義
FOREIGN KEY制約はテーブル定義に追加して利用します。
基本構文
CREATE TABLE テーブル名 (
カラム定義
FOREIGN KEY ( カラム名, ... ) REFERENCES 参照先テーブル名( 参照先カラム名, ... )
);
FOREIGN KEY ( カラム名, ... ) REFERENCES 参照先テーブル名( 参照先カラム名, ... )
の部分がFOREIGN KEY制約の記述です。
FOREIGN KEY ( カラム名, ... )
の丸括弧(()
)内に外部キーとするカラム、参照先テーブル名( 参照先カラム名, ... )
の丸括弧(()
)内に参照先のカラムを定義し、複数ある場合はカンマ(,
)で区切ります。
サンプル
mysql> /* ショップテーブル */
mysql> CREATE TABLE shops (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO shops ( name )
-> VALUES ( 'Fruits Shop' ), ( 'Food Shop' ), ( 'Dessert Shop' );
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM shops;
+----+--------------+
| id | name |
+----+--------------+
| 1 | Fruits Shop |
| 2 | Food Shop |
| 3 | Dessert Shop |
+----+--------------+
3 rows in set (0.00 sec)
mysql> /* 商品テーブル */
mysql> CREATE TABLE items (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20),
-> price INT,
-> stock INT,
-> shop_id INT,
___ih_hl_start
-> FOREIGN KEY ( shop_id ) REFERENCES shops(id)
___ih_hl_end
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO items ( name, price, stock, shop_id ) VALUES
-> ( 'Apple', 200, 10, 1 ),
-> ( 'Orange', 150, 3, 1 ),
-> ( 'Pineapple', 1100, 3, 2 ),
-> ( 'Grapes', 3500, 1, 3 );
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name | price | stock | shop_id |
+----+-----------+-------+-------+---------+
| 1 | Apple | 200 | 10 | 1 |
| 2 | Orange | 150 | 3 | 1 |
| 3 | Pineapple | 1100 | 3 | 2 |
| 4 | Grapes | 3500 | 1 | 3 |
+----+-----------+-------+-------+---------+
4 rows in set (0.00 sec)
mysql> INSERT INTO items ( name, price, stock, shop_id ) VALUE ( 'Apple', 200, 10, 4 );
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`items`, CONSTRAINT `items_ibfk_1` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`))
ショップテーブルと商品テーブルを作成し、商品テーブルにショップテーブルの外部キーを設定しました。(32行目)
最後のデータ追加(INSERT INTO VALUE
)では、ショップテーブルに存在しない値を外部キーに設定したためエラーが発生しました。
確認
外部キーはSHOW CREATE TABLE
で確認できます。
mysql> SHOW CREATE TABLE items;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| items | CREATE TABLE `items` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`price` int DEFAULT NULL,
`stock` int DEFAULT NULL,
`shop_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `shop_id` (`shop_id`),
CONSTRAINT `items_ibfk_1` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
参照データの更新・削除
初期設定では外部キーの参照データは変更・削除ができません。
例えば次のようなケースです。
mysql> SELECT * FROM shops;
+----+--------------+
| id | name |
+----+--------------+
| 1 | Fruits Shop |
| 2 | Food Shop |
| 3 | Dessert Shop |
+----+--------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name | price | stock | shop_id |
+----+-----------+-------+-------+---------+
| 1 | Apple | 200 | 10 | 1 |
| 2 | Orange | 150 | 3 | 1 |
| 3 | Pineapple | 1100 | 3 | 2 |
+----+-----------+-------+-------+---------+
3 rows in set (0.00 sec)
mysql> UPDATE shops SET id = 99 WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE shops SET id = 99 WHERE id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`items`, CONSTRAINT `items_ibfk_1` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`))
shops
テーブルのid
を変更するクエリを2回実行しました。
1回目(21行目)はid
が3
のレコードに対して実行し、正常に処理されましたが、2回目(25行目)はエラーが発生しています。
この違いは外部キーの参照データであるかどうかで、items
テーブルでid
が3
のフィールドは参照されていませんが、2
は参照されています。
この『エラーの発生』の処理は変更することができます。
更新時の処理
参照データが更新された場合の処理はON UPDATE
で設定します。
基本構文
CREATE TABLE テーブル名 (
カラム定義
FOREIGN KEY ( カラム名, ... ) REFERENCES 参照先テーブル名( 参照先カラム名, ... ) ON UPDATE オプション
);
FOREIGN KEY制約の最後にON UPDATE オプション
を追記します。
設定可能なオプションは主に次の3つです。
オプション | 内容 |
---|---|
RESTRICT | エラーを発生します。初期値です。 |
CASCADE | 外部キーの値も変更します。 |
SET NULL | 外部キーの値をNULLに設定します。 |
サンプル
mysql> CREATE TABLE items (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20),
-> price INT,
-> stock INT,
-> shop_id INT,
___ih_hl_start
-> FOREIGN KEY ( shop_id ) REFERENCES shops(id) ON UPDATE CASCADE
___ih_hl_end
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO items ( name, price, stock, shop_id ) VALUES
-> ( 'Apple', 200, 10, 1 ),
-> ( 'Orange', 150, 3, 1 ),
-> ( 'Pineapple', 1100, 3, 2 );
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM shops;
+----+--------------+
| id | name |
+----+--------------+
| 1 | Fruits Shop |
| 2 | Food Shop |
| 3 | Dessert Shop |
+----+--------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name | price | stock | shop_id |
+----+-----------+-------+-------+---------+
| 1 | Apple | 200 | 10 | 1 |
| 2 | Orange | 150 | 3 | 1 |
| 3 | Pineapple | 1100 | 3 | 2 |
+----+-----------+-------+-------+---------+
3 rows in set (0.00 sec)
mysql> UPDATE shops SET id = 99 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM shops;
+----+--------------+
| id | name |
+----+--------------+
| 2 | Food Shop |
| 3 | Dessert Shop |
| 99 | Fruits Shop |
+----+--------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name | price | stock | shop_id |
+----+-----------+-------+-------+---------+
| 1 | Apple | 200 | 10 | 99 |
| 2 | Orange | 150 | 3 | 99 |
| 3 | Pineapple | 1100 | 3 | 2 |
+----+-----------+-------+-------+---------+
3 rows in set (0.00 sec)
7行目のFOREIGN KEY制約で参照データが更新された場合の処理をCASCADE
に設定しています。
38行目でshops
テーブルの参照データを更新しましたが、CASCADE
に従ってitems
テーブルの外部キーの値も更新されています。
削除時の処理
参照データが削除された場合の処理はON DELETE
で設定します。
基本構文
CREATE TABLE テーブル名 (
カラム定義
FOREIGN KEY ( カラム名, ... ) REFERENCES 参照先テーブル名( 参照先カラム名, ... ) ON DELETE オプション
);
FOREIGN KEY制約の最後にON DELETE オプション
を追記します。
設定可能なオプションは主に次の3つです。
オプション | 内容 |
---|---|
RESTRICT | エラーを発生します。初期値です。 |
CASCADE | 外部キーのレコードも削除します。 |
SET NULL | 外部キーの値をNULLに設定します。 |
また、ON UPDATE
と合わせて設定することも可能です。
FOREIGN KEY ( カラム名, ... ) REFERENCES 参照先テーブル名( 参照先カラム名, ... ) ON UPDATE オプション ON DELETE オプション
サンプル
mysql> CREATE TABLE items (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20),
-> price INT,
-> stock INT,
-> shop_id INT,
___ih_hl_start
-> FOREIGN KEY ( shop_id ) REFERENCES shops(id) ON DELETE CASCADE
___ih_hl_end
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO items ( name, price, stock, shop_id ) VALUES
-> ( 'Apple', 200, 10, 1 ),
-> ( 'Orange', 150, 3, 1 ),
-> ( 'Pineapple', 1100, 3, 2 );
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM shops;
+----+--------------+
| id | name |
+----+--------------+
| 1 | Fruits Shop |
| 2 | Food Shop |
| 3 | Dessert Shop |
+----+--------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name | price | stock | shop_id |
+----+-----------+-------+-------+---------+
| 1 | Apple | 200 | 10 | 1 |
| 2 | Orange | 150 | 3 | 1 |
| 3 | Pineapple | 1100 | 3 | 2 |
+----+-----------+-------+-------+---------+
3 rows in set (0.00 sec)
mysql> DELETE FROM shops WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM shops;
+----+--------------+
| id | name |
+----+--------------+
| 2 | Food Shop |
| 3 | Dessert Shop |
+----+--------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM items;
+----+-----------+-------+-------+---------+
| id | name | price | stock | shop_id |
+----+-----------+-------+-------+---------+
| 3 | Pineapple | 1100 | 3 | 2 |
+----+-----------+-------+-------+---------+
1 row in set (0.00 sec)
7行目のFOREIGN KEY制約で参照データが削除された場合の処理をCASCADE
に設定しています。
38行目でshops
テーブルの参照データを削除しており、CASCADE
に従ってitems
テーブルの外部キーの値に削除した参照データを持つレコードが削除されています。