外部キーとは？ 主キー・候補キー・参照整合性を体系的に理解する

1. なぜデータベースの表を分けるのか

オンラインショップで、注文が入るたびに「顧客の住所・名前・商品の価格・商品名」を全部1行へコピーするとします。同じ顧客が100回注文すれば住所が100か所に複製されます。引っ越しのたびに100か所を書き換えるのは大変ですし、更新漏れがあると異なる住所が並びます。これが更新時異常の例です。

そこで顧客・商品・注文を別々のテーブルにします。注文には顧客や商品そのものではなく、対応する行の識別子を記録します。これが外部キーの基本的な考え方です。

2. 主キー・候補キー・外部キーの役割

3. SQLで参照整合性を宣言する

顧客と商品を別々に管理し、注文は両方を参照します。REFERENCES が外部キー宣言の中心です。

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(80) NOT NULL
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(80) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (product_id)  REFERENCES products(id)
);

上の注文表には、顧客向けと商品向けの2つの外部キー制約が共存します。さらに担当社員や配送先を参照する列が必要なら追加できます。

同じ値の注文を何度登録してもよい

INSERT INTO customers VALUES (1, '佐藤');
INSERT INTO products  VALUES (10, '書籍');

INSERT INTO orders VALUES (101, 1, 10, 2); -- 登録できる
INSERT INTO orders VALUES (102, 1, 10, 1); -- 同じ外部キーでも登録できる
INSERT INTO orders VALUES (103, 99, 10, 1); -- 顧客99がなければエラー

4. 参照先を消そうとしたらどうなる？

顧客に紐づく注文が残っている状態で顧客を削除すると、参照整合性を壊す可能性があります。このときの挙動は外部キーの ON DELETE で指定できます。

CASCADEは便利ですが、注文履歴や監査記録の消失につながる設計では危険です。運用要件と復旧可能性を考えて選びます。実際の挙動はDBMS仕様および制約の定義に従います。

5. 複合外部キーと自己参照

外部キーは1列に限りません。「会社IDと部署IDの組」で部署を一意に識別する場合、社員表からその2列の組を参照できます。参照元・参照先で列数が一致し、互換性のある型や比較規則を使う必要があります。

CREATE TABLE departments (
  company_id INTEGER NOT NULL,
  department_id INTEGER NOT NULL,
  PRIMARY KEY (company_id, department_id)
);
CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  company_id INTEGER NOT NULL,
  department_id INTEGER NOT NULL,
  FOREIGN KEY (company_id, department_id)
    REFERENCES departments (company_id, department_id)
);

同一表を参照することもできます。たとえば employees.manager_id REFERENCES employees(employee_id) とすれば、社員が上司の社員IDを保持できます。ただし、組織階層の循環などの業務ルールは外部キーだけでは防げません。

6. 外部キーとJOINは別の仕組み

外部キーは登録・更新・削除時の整合性を守る制約です。JOINは検索時に表同士を結び付ける操作です。外部キー制約がなくてもJOINは書けますし、外部キーを定義してもJOINを自動実行するわけではありません。

SELECT o.id, c.name AS customer, p.name AS product
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products AS p ON p.id = o.product_id;

セキュリティとのつながり

外部キーが防ぐのは主に存在しないデータへの参照であり、閲覧・更新の権限を制御する仕組みではありません。権限管理には GRANT、認証、アプリケーション側の認可検証などが別途必要です。また外部キーの参照元列に索引を付けるかは性能設計の問題で、DBMSによって自動作成されるとは限りません。

7. 外部キーの操作ラボ

架空の注文DBを使って、同じ顧客による複数注文、存在しない顧客の参照、削除時の動作を再現します。実際のDBサーバーに接続せず、ブラウザ内だけで判定する学習用シミュレーターです。

8. 覚えておきたい整理

• 主キーは行を一意に識別し、外部キーは参照先との関係を示す。
• 外部キーは参照元テーブルで重複してよい。1つの表に複数の外部キーを定義できる。
• NULLを禁止したい場合は NOT NULL を併用する。
• 参照先の削除に対する挙動は ON DELETE で変わる。
• 外部キーは整合性の仕組みであり、JOIN・アクセス権限・暗号化とは役割が違う。