[SQLのはじめ方] Part17: 主キー・外部キー制約（PRIMARY KEY, FOREIGN KEY）

データベース設計において、データの整合性とテーブル間の関連付けは非常に重要です。それを実現するための重要な仕組みが「制約」です。今回は、数ある制約の中でも特に重要な主キー（PRIMARY KEY）と外部キー（FOREIGN KEY）について学びましょう！ ✨

主キー（PRIMARY KEY）とは？

主キーは、テーブル内の各行（レコード）を一意に識別するための特別なカラム、またはカラムの組み合わせです。テーブルに一つだけ設定できます。

主キーには以下の重要な特徴があります。

一意性（Uniqueness）: 主キーに設定されたカラムの値は、テーブル内で絶対に重複してはいけません。
非NULL（Not Null）: 主キーのカラムにはNULL値（値が存在しない状態）を入れることはできません。必ず何らかの値が必要です。

これらの特性により、主キーは特定のレコードを確実に特定するための「目印」として機能します。例えば、「社員番号」や「商品コード」などが主キーとしてよく使われます。

主キーの設定方法

主キーは、テーブル作成時（CREATE TABLE）またはテーブル作成後（ALTER TABLE）に設定できます。

テーブル作成時に設定する例

-- 方法1: カラム定義の直後に PRIMARY KEY を指定
CREATE TABLE employees (
    employee_id INT PRIMARY KEY, -- 社員IDを主キーに設定
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

-- 方法2: テーブル定義の最後に CONSTRAINT を使って指定（複合主キーも可能）
CREATE TABLE order_items (
    order_id INT,
    item_id INT,
    quantity INT,
    -- order_id と item_id の組み合わせで一意になるように複合主キーを設定
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, item_id)
);

CONSTRAINT pk_order_items のように、制約に名前を付けておくと、後で管理しやすくなります。

既存のテーブルに追加する例

-- departments テーブルに主キーを追加
ALTER TABLE departments
ADD CONSTRAINT pk_departments PRIMARY KEY (department_id);

主キーを追加する場合、対象カラムの値が既に一意であり、かつNULLでないことを確認する必要があります。

外部キー（FOREIGN KEY）とは？ 🔗

外部キーは、あるテーブルのカラム（またはカラムの組み合わせ）が、別のテーブルの主キー（またはUNIQUE制約を持つカラム）を参照するための制約です。これにより、テーブル間の関連性を定義し、参照整合性を保つことができます。

例えば、「注文」テーブルに「顧客ID」というカラムがあり、それが「顧客」テーブルの主キーである「顧客ID」を参照している場合、この「注文」テーブルの「顧客ID」が外部キーとなります。

外部キーの主な目的は以下の通りです。

関連性の保証: 存在する顧客のIDしか注文テーブルに登録できないようにするなど、関連するデータ間の整合性を保ちます。
不正なデータの防止: 存在しないデータを参照しようとする操作を防ぎます。

外部キーの設定方法

外部キーも、テーブル作成時またはテーブル作成後に設定できます。

テーブル作成時に設定する例

-- まず、参照される側のテーブル (親テーブル) を作成
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

-- 次に、参照する側のテーブル (子テーブル) を作成
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT, -- このカラムが外部キーとなる
    -- 外部キー制約を設定
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id) -- このテーブルの customer_id カラムが
        REFERENCES customers(customer_id) -- customers テーブルの customer_id カラムを参照する
);

外部キーを設定する前に、参照先のテーブルとカラム（通常は主キー）が既に存在している必要があります。

既存のテーブルに追加する例

-- products テーブル (参照される側)
CREATE TABLE products (
    product_code VARCHAR(10) PRIMARY KEY,
    product_name VARCHAR(100)
);

-- order_details テーブル (参照する側) ※最初は外部キーなしで作成
CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT,
    product_code VARCHAR(10), -- 後で外部キーにするカラム
    quantity INT
);

-- order_details テーブルに外部キーを追加
ALTER TABLE order_details
ADD CONSTRAINT fk_details_products
FOREIGN KEY (product_code) REFERENCES products(product_code);

参照整合性アクション (ON DELETE / ON UPDATE)

外部キーを設定する際には、参照先のデータが削除（DELETE）または更新（UPDATE）された場合に、参照元のデータをどう扱うかを指定できます。主なオプションは以下の通りです。

アクション	説明
`RESTRICT` / `NO ACTION` (デフォルト)	参照元のテーブルから参照されている行が参照先のテーブルにある場合、その参照先の行の削除や更新を許可しません（エラーになります）。
`CASCADE`	参照先の行が削除されると、それを参照している参照元の行も一緒に削除されます。参照先のキーが更新されると、参照元の外部キーの値も一緒に更新されます。
`SET NULL`	参照先の行が削除または更新されると、参照元の外部キーカラムの値が`NULL`に設定されます。外部キーカラムが`NULL`を許可している必要があります。
`SET DEFAULT`	参照先の行が削除または更新されると、参照元の外部キーカラムの値が定義済みのデフォルト値に設定されます。外部キーカラムに`DEFAULT`制約が必要です。

参照アクションを指定する例

-- orders テーブル作成時に参照アクションを指定
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE SET NULL -- 顧客が削除されたら、注文の customer_id を NULL にする
        ON UPDATE CASCADE  -- 顧客IDが変更されたら、注文の customer_id も追従して変更する
);

どのアクションを選択するかは、データの性質やアプリケーションの要件によって慎重に決定する必要があります。

まとめ

主キーと外部キーは、リレーショナルデータベースにおいてデータの一意性と関連性を保つための基本的ながら非常に強力な仕組みです 💪。

主キー (PRIMARY KEY): テーブル内の行を一意に識別する。NULL不可、重複不可。
外部キー (FOREIGN KEY): 他のテーブルの主キーを参照し、テーブル間の関連を定義する。参照整合性を維持する。

これらの制約を適切に設計し利用することで、データの矛盾を防ぎ、信頼性の高いデータベースを構築することができます。次のステップに進む前に、これらの概念をしっかり理解しておきましょう！