【MySQL】FOREIGN KEY REFERENCES制約 - 外部キー(他テーブルの値)

【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行目)はid3のレコードに対して実行し、正常に処理されましたが、2回目(25行目)はエラーが発生しています。

この違いは外部キーの参照データであるかどうかで、itemsテーブルでid3のフィールドは参照されていませんが、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テーブルの外部キーの値に削除した参照データを持つレコードが削除されています。