外部キーと参照キーで作る堅牢なデータベース：事例とベストプラクティス
SQLにおける外部キーと参照キーの違い
SQLデータベースにおいて、関連するテーブル間のデータ整合性を保つために重要な役割を果たすのが「外部キー」と「参照キー」です。一見同じような名称ですが、実は微妙な違いがあります。
本記事では、「外部キー」と「参照キー」の違いを分かりやすく解説し、それぞれの役割と具体的な設定方法について説明します。
外部キーと参照キー：定義
- 外部キー（Foreign Key）：あるテーブル（子テーブル）の列が、別のテーブル（親テーブル）の主キーを参照する制約です。子テーブルのデータ整合性を保ち、関連性を明確にするために使用されます。
- 参照キー（Referencing Key）：外部キーと同義で使用される言葉です。どちらの用語も広く使われています。
厳密には、外部キーと参照キーは次のような違いがあります。
- 外部キー: 子テーブルから親テーブルへの参照関係を定義する制約そのものを指します。
- 参照キー: 外部キーを構成する列を指します。つまり、外部キー制約の中で、親テーブルの主キーを参照する列のことです。
しかし、多くの場合、「外部キー」と「参照キー」は同義語として混用されています。
外部キーの役割
外部キーは以下の3つの重要な役割を果たします。
- データ整合性の保全: 子テーブルに存在するデータは、必ず親テーブルに存在するデータを参照するように制約することで、データの論理的な整合性を保ちます。
- 関連性の明確化: どのデータがどのデータと関連しているのかをデータベース構造上で明確にし、データ間の関係性を理解しやすくします。
- データ更新・削除の制御: 親テーブルのデータが更新・削除される場合、関連する子テーブルのデータにも適切な処理を施すように制御することで、データの不整合を防ぎます。
外部キーの設定方法
外部キーは、テーブル作成時または既存のテーブルに対して後から設定することができます。
1 テーブル作成時
CREATE TABLE 子テーブル名 (
列名1 型,
列名2 型,
...,
外部キー列名 型,
CONSTRAINT 外部キー名 FOREIGN KEY (外部キー列名)
REFERENCES 親テーブル名(親キー列名)
);
2 既存テーブルへの設定
ALTER TABLE 子テーブル名
ADD CONSTRAINT 外部キー名 FOREIGN KEY (外部キー列名)
REFERENCES 親テーブル名(親キー列名);
まとめ
SQLにおける外部キーと参照キーは、データ整合性を保ち、関連性を明確にするために重要な役割を果たします。概念的には小さな違いがありますが、実務上は同義語として扱われることが多いです。
外部キーを正しく設定することで、データベースの信頼性と保守性を向上させることができます。
補足
- 外部キー制約には、ON DELETEとON UPDATEオプションを設定することで、親テーブルのデータが削除・更新された際に、子テーブルのデータにどのような処理を施すかを指定することができます。
- 外部キー以外にも、データの整合性を保つために使用される制約として、主キー制約、ユニーク制約、チェック制約などがあります。
サンプルコード：顧客注文管理システム
このサンプルコードでは、顧客注文管理システムにおける外部キーと参照キーの使用方法を説明します。
要件
- 顧客テーブル：顧客ID、顧客名、住所
- 商品テーブル：商品ID、商品名、単価
- 注文テーブル：注文ID、顧客ID、商品ID、注文個数
テーブル定義
-- 顧客テーブル
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
);
-- 商品テーブル
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255) NOT NULL,
unit_price DECIMAL(10,2) NOT NULL
);
-- 注文テーブル
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
説明
- 顧客テーブル:
customer_id: 主キー（自動採番）customer_name: 顧客名address: 住所
- 商品テーブル:
product_name: 商品名unit_price: 単価
- 注文テーブル:
customer_id: 顧客ID（外部キー）customersテーブルのcustomer_idを参照
product_id: 商品ID（外部キー）productsテーブルのproduct_idを参照
quantity: 注文個数
ordersテーブルのcustomer_id列は、customersテーブルのcustomer_id列を参照する外部キーです。- これにより、
ordersテーブルに登録される顧客IDは、必ずcustomersテーブルに存在する顧客IDであることが保証されます。
- これにより、
制約の確認
-- 顧客IDが存在しない注文の挿入を試みる
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (9999, 1, 10);
-- エラーが発生する:
-- ... SQLSTATE[23000]: Integrity constraint violation: cannot insert a value into table 'orders' that doesn't exist in table 'customers' for column 'customer_id'
-- 商品IDが存在しない注文の挿入を試みる
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (1, 9999, 10);
-- エラーが発生する:
-- ... SQLSTATE[23000]: Integrity constraint violation: cannot insert a value into table 'orders' that doesn't exist in table 'products' for column 'product_id'
外部キーと参照キーのもう一つの説明方法
SQLにおける外部キーと参照キーは、関連するテーブル間のデータ整合性を保つために重要な役割を果たします。
本記事では、外部キーと参照キーの概念を別の視点から説明し、理解を深めるための補足情報と代替表現を紹介します。
わかりやすい例え
図書館システムを例に考えてみましょう。
- 書籍テーブル: 書籍ID、書籍名、著者名、ジャンル
- 貸出テーブル: 貸出ID、会員ID、書籍ID、貸出日、返却予定日
この場合、貸出テーブルの書籍IDは書籍テーブルの書籍IDを参照する外部キーとなります。
書籍IDは、貸出テーブル内で書籍を識別する固有の番号です。しかし、書籍IDがどの書籍に対応しているのかを知るためには、書籍テーブルを参照する必要があります。
つまり、外部キーは、あるテーブルのデータが別のテーブルのデータに関連していることを示す親子関係のようなものです。
補足情報
- 外部キー制約: 外部キーを設定することで、データベースに制約を設け、データの整合性を保ちます。具体的には、以下のことが保証されます。
- 子テーブルに存在するデータは、必ず親テーブルに存在するデータを参照していること
- 親テーブルのデータが削除・更新された場合、関連する子テーブルのデータにも適切な処理が施されること
- 参照整合性: 外部キー制約によって保証されるデータの整合性を参照整合性といいます。
- 主従関係: 外部キーが存在するテーブル間の関係を主従関係といいます。
- 参照される側のテーブルが主テーブル、参照する側のテーブルが従テーブルとなります。
代替表現
- 外部参照: 外部キーを外部参照と表現することもできます。
sql database foreign-keys
