第3章 キーと制約

1. キーの種類

1.1 主キー

主キー（Primary Key）は、リレーション内の各タプルを一意に識別するための属性または属性の組み合わせである。

Table 1. 主キーの特性

-- 主キーの定義例
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,  -- 単一属性の主キー
    name VARCHAR(100) NOT NULL,
    department_id INTEGER
);

-- 複合主キー（複数の属性で構成）
CREATE TABLE order_details (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)  -- 複合主キー
);

1.2 外部キー

外部キー（Foreign Key）は、他のリレーションの主キーを参照する属性である。リレーション間の関連付けを実現し、参照整合性を保証する。

Fig. 1. 外部キーによる参照関係

Table 2. 外部キーの役割

-- 外部キーの定義例
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

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

-- ON DELETE / ON UPDATE オプション
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE SET NULL      -- 親削除時にNULLを設定
        ON UPDATE CASCADE       -- 親更新時に連動更新
);

Table 3. 外部キーの参照アクション

1.3 候補キー・代理キー

候補キー（Candidate Key）：主キーになりうる属性または属性の組み合わせ。一意性と最小性を満たす。

代理キー（Surrogate Key）：業務上の意味を持たない人工的なキー。自動採番される連番やUUIDが典型例。

Table 4. キーの種類と特徴

2. 制約

制約（Constraint）は、データの整合性を保証するためのルールである。RDBMSは制約違反を検出し、不正なデータの挿入・更新を防止する。

2.1 一意制約

一意制約（UNIQUE Constraint）は、指定した属性の値が重複しないことを保証する。主キーとは異なり、NULL値を許容する（ただしDBMSにより動作が異なる）。

-- 一意制約の定義
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email VARCHAR(255) UNIQUE,      -- 単一列の一意制約
    username VARCHAR(50) UNIQUE
);

-- 複合一意制約
CREATE TABLE subscriptions (
    subscription_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    service_id INTEGER,
    UNIQUE (user_id, service_id)    -- 組み合わせで一意
);

2.2 NOT NULL制約

NOT NULL制約は、属性がNULL値を持つことを禁止する。必須項目の入力を強制する際に使用する。

-- NOT NULL制約の定義
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name VARCHAR(200) NOT NULL,       -- 必須
    price DECIMAL(10,2) NOT NULL,     -- 必須
    description TEXT                   -- 任意（NULLを許容）
);

2.3 CHECK制約

CHECK制約は、属性値が指定した条件を満たすことを保証する。ドメイン（値の範囲）を制限する際に使用する。

-- CHECK制約の定義
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER CHECK (age >= 18 AND age <= 120),
    salary DECIMAL(10,2) CHECK (salary >= 0),
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')),
    hire_date DATE CHECK (hire_date >= '1900-01-01')
);

-- 複数列を参照するCHECK制約
CREATE TABLE contracts (
    contract_id INTEGER PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CHECK (end_date > start_date)   -- 終了日は開始日より後
);

Table 5. 制約の種類まとめ

3. 参照整合性

参照整合性（Referential Integrity）は、外部キーが参照する値が、参照先テーブルに存在することを保証する制約である。

Table 6. 参照整合性違反の例

-- 参照整合性の例
-- 1. 部署テーブル（親）
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 2. 社員テーブル（子）
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 正常なINSERT
INSERT INTO departments VALUES (1, '営業部');
INSERT INTO employees VALUES (101, '田中太郎', 1);  -- OK

-- 参照整合性違反（エラー）
INSERT INTO employees VALUES (102, '鈴木花子', 99);  -- 部署ID 99は存在しない
-- ERROR: Foreign key constraint violated

4. まとめ

Table 7. 第3章のまとめ