PostgreSQL 外部キー（Foreign Key）追加の完全ガイド – データの整合性を守る方法

データベース・SQL

データベースを使っていて、こんな悩みはありませんか？

「注文テーブルに存在しないユーザーIDが入ってしまった…」 「商品を削除したのに、在庫テーブルにデータが残っている…」

これらの問題を防ぐのが「外部キー」という仕組みです。

外部キーは、テーブル間の関係を定義して、データの整合性を自動的に保ってくれる便利な機能。簡単に言うと、「このデータは、あっちのテーブルに必ず存在するデータと紐づいているよ」という約束事を作るんです。

この記事では、PostgreSQLで外部キーを追加する方法を、基本から応用まで、実例を交えながら解説していきます。

スポンサーリンク

外部キーの基本概念を理解しよう

外部キーとは？

外部キー（Foreign Key）は、あるテーブルの列が、別のテーブルの主キー（Primary Key）を参照する仕組みです。

レストランの予約システムで例えると：

顧客テーブル（customers）
- customer_id（主キー）
- name
- phone

予約テーブル（reservations）
- reservation_id
- customer_id（外部キー → 顧客テーブルのcustomer_idを参照）
- date
- time

予約テーブルのcustomer_idは、必ず顧客テーブルに存在するcustomer_idでなければいけません。これが外部キーの役割です。

なぜ外部キーが必要？

外部キーがない場合の問題：

  1. 存在しないデータの参照
    • 顧客ID: 999の予約を作れてしまう（そんな顧客いないのに！）
  2. 孤立したデータ
    • 顧客を削除しても、予約だけ残ってしまう
  3. データの不整合
    • 手動でデータを管理すると、ミスが起きやすい

外部キーがある場合のメリット：

  1. 参照整合性の保証
    • 存在しないデータは参照できない
  2. カスケード操作
    • 親データを削除したら、子データも自動削除
  3. データベースレベルでの保護
    • アプリケーションのバグがあっても、DBが守ってくれる

外部キーを追加する基本的な方法

方法1：テーブル作成時に外部キーを定義

新しくテーブルを作るときに、最初から外部キーを設定する方法です。

-- 親テーブル：部門テーブル
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

-- 子テーブル：従業員テーブル（外部キー付き）
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INTEGER NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE,
    -- 外部キー制約を定義
    CONSTRAINT fk_department
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
);

ポイント：

  • CONSTRAINT fk_department で制約に名前を付けている（後で管理しやすい）
  • FOREIGN KEY (department_id) で外部キーとなる列を指定
  • REFERENCES departments(department_id) で参照先を指定

方法2：既存テーブルに外部キーを追加

すでに存在するテーブルに、後から外部キーを追加する方法です。

-- すでに存在するテーブルに外部キーを追加
ALTER TABLE employees 
ADD CONSTRAINT fk_department 
FOREIGN KEY (department_id) 
REFERENCES departments(department_id);

注意点： 追加する前に、データの整合性を確認しましょう！

-- 外部キー追加前の確認：存在しないdepartment_idがないかチェック
SELECT DISTINCT e.department_id
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

方法3：列の追加と同時に外部キーを設定

新しい列を追加するときに、同時に外部キーも設定できます。

-- manager_id列を追加して、自己参照の外部キーを設定
ALTER TABLE employees 
ADD COLUMN manager_id INTEGER,
ADD CONSTRAINT fk_manager 
    FOREIGN KEY (manager_id) 
    REFERENCES employees(employee_id);

カスケードオプション：親データが変更されたらどうする？

ON DELETE オプション

親レコードが削除されたときの動作を指定します。

CASCADE（カスケード削除）

親が削除されたら、子も自動削除。

-- 部門が削除されたら、その部門の従業員も削除
ALTER TABLE employees 
ADD CONSTRAINT fk_department_cascade
FOREIGN KEY (department_id) 
REFERENCES departments(department_id)
ON DELETE CASCADE;

使用例： 注文と注文明細、ブログ記事とコメントなど

SET NULL（NULL設定）

親が削除されたら、子の外部キー列をNULLに。

-- 部門が削除されたら、従業員のdepartment_idをNULLに
ALTER TABLE employees 
ALTER COLUMN department_id DROP NOT NULL,  -- まずNULL許可に変更
ADD CONSTRAINT fk_department_setnull
FOREIGN KEY (department_id) 
REFERENCES departments(department_id)
ON DELETE SET NULL;

使用例： カテゴリーと商品（カテゴリーなしでも商品は残したい）

RESTRICT（制限）

子レコードがある限り、親を削除できない（デフォルト）。

-- 従業員がいる部門は削除できない
ALTER TABLE employees 
ADD CONSTRAINT fk_department_restrict
FOREIGN KEY (department_id) 
REFERENCES departments(department_id)
ON DELETE RESTRICT;

使用例： 重要なマスターデータの保護

SET DEFAULT（デフォルト値設定）

親が削除されたら、子の外部キー列をデフォルト値に。

-- デフォルト部門を設定
ALTER TABLE employees 
ALTER COLUMN department_id SET DEFAULT 1,  -- 部門ID:1をデフォルトに
ADD CONSTRAINT fk_department_default
FOREIGN KEY (department_id) 
REFERENCES departments(department_id)
ON DELETE SET DEFAULT;

ON UPDATE オプション

親の主キーが更新されたときの動作を指定します。

-- 部門IDが変更されたら、従業員の部門IDも自動更新
ALTER TABLE employees 
ADD CONSTRAINT fk_department_update
FOREIGN KEY (department_id) 
REFERENCES departments(department_id)
ON UPDATE CASCADE
ON DELETE RESTRICT;

実践的な例：ECサイトのデータベース

シナリオ：オンラインショップのDB設計

実際のECサイトを想定して、外部キーを設定してみましょう。

-- 1. ユーザーテーブル
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 商品カテゴリーテーブル
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    parent_category_id INTEGER,
    -- 自己参照の外部キー（階層構造）
    CONSTRAINT fk_parent_category
        FOREIGN KEY (parent_category_id)
        REFERENCES categories(category_id)
        ON DELETE CASCADE
);

-- 3. 商品テーブル
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    category_id INTEGER,
    stock_quantity INTEGER DEFAULT 0,
    -- カテゴリーが削除されてもproductは残す
    CONSTRAINT fk_product_category
        FOREIGN KEY (category_id)
        REFERENCES categories(category_id)
        ON DELETE SET NULL
);

-- 4. 注文テーブル
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending',
    -- ユーザーが削除できないように制限
    CONSTRAINT fk_order_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE RESTRICT
);

-- 5. 注文明細テーブル
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL,
    -- 注文が削除されたら明細も削除
    CONSTRAINT fk_order_item_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON DELETE CASCADE,
    -- 商品は削除できないように制限
    CONSTRAINT fk_order_item_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE RESTRICT
);

-- 6. レビューテーブル
CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    rating INTEGER CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 商品が削除されたらレビューも削除
    CONSTRAINT fk_review_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE CASCADE,
    -- ユーザーが削除されたらレビューも削除
    CONSTRAINT fk_review_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE,
    -- 同じユーザーが同じ商品に複数レビューできないように
    CONSTRAINT unique_user_product_review UNIQUE (product_id, user_id)
);

外部キーの管理と確認

外部キー一覧を確認する

-- 特定テーブルの外部キー制約を確認
SELECT
    tc.constraint_name,
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name,
    rc.delete_rule,
    rc.update_rule
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints AS rc
    ON rc.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY' 
    AND tc.table_name = 'employees';

外部キーの削除

-- 外部キー制約を削除
ALTER TABLE employees 
DROP CONSTRAINT fk_department;

-- 制約名が分からない場合は、先に確認してから削除
-- \d employees （psqlコマンド）で制約名を確認

外部キーの無効化と有効化

大量データのインポート時など、一時的に外部キーチェックを無効にしたい場合：

-- 外部キー制約を一時的に無効化（削除せずに）
ALTER TABLE employees 
DISABLE TRIGGER ALL;

-- データのインポートなどを実行

-- 外部キー制約を再度有効化
ALTER TABLE employees 
ENABLE TRIGGER ALL;

注意： この操作は慎重に！データの整合性が崩れる可能性があります。

トラブルシューティング

エラー1：参照先のテーブルまたは列が存在しない

ERROR: relation "departments" does not exist

解決法：

  1. テーブル名のスペルを確認
  2. スキーマ名を含めて指定
  3. 参照先テーブルが先に作成されているか確認
-- スキーマを含めて指定
ALTER TABLE public.employees 
ADD CONSTRAINT fk_department 
FOREIGN KEY (department_id) 
REFERENCES public.departments(department_id);

エラー2：データ型が一致しない

ERROR: foreign key constraint "fk_department" cannot be implemented
DETAIL: Key columns "department_id" and "department_id" are of incompatible types

解決法： データ型を確認して統一する

-- データ型を確認
\d employees
\d departments

-- 必要なら型を変更
ALTER TABLE employees 
ALTER COLUMN department_id TYPE INTEGER;

エラー3：既存データが制約に違反している

ERROR: insert or update on table "employees" violates foreign key constraint

解決法： 違反しているデータを修正または削除

-- 違反データを特定
SELECT * FROM employees 
WHERE department_id NOT IN (
    SELECT department_id FROM departments
);

-- 違反データを修正
UPDATE employees 
SET department_id = 1  -- 存在する部門IDに変更
WHERE department_id NOT IN (
    SELECT department_id FROM departments
);

-- または違反データを削除
DELETE FROM employees 
WHERE department_id NOT IN (
    SELECT department_id FROM departments
);

エラー4：インデックスが不足している

外部キーの参照先には自動的にインデックスが必要ですが、参照元にはありません。

-- パフォーマンス改善のため、外部キー列にインデックスを作成
CREATE INDEX idx_employees_department_id 
ON employees(department_id);

-- 複合外部キーの場合
CREATE INDEX idx_order_items_order_product 
ON order_items(order_id, product_id);

パフォーマンスへの影響と対策

外部キーがパフォーマンスに与える影響

メリット：

  • JOINクエリが最適化される
  • データの整合性が保証される

デメリット：

  • INSERT/UPDATE/DELETE時にチェック処理が入る
  • 大量データ処理時に遅くなる可能性

パフォーマンス最適化のテクニック

1. 適切なインデックスの作成

-- 外部キー列にインデックスを作成
CREATE INDEX idx_fk_department ON employees(department_id);

-- 参照の多い複合キーにもインデックス
CREATE INDEX idx_user_date ON orders(user_id, order_date);

2. バッチ処理時の一時無効化

-- トランザクション内で一時的に制約を遅延
BEGIN;
SET CONSTRAINTS ALL DEFERRED;

-- 大量のINSERT/UPDATE処理
INSERT INTO employees (employee_name, department_id) 
VALUES 
    ('山田太郎', 1),
    ('佐藤花子', 2),
    -- ... 大量のデータ
    ('鈴木一郎', 3);

-- コミット時に制約チェック
COMMIT;

3. パーティションテーブルでの外部キー

-- パーティションテーブルでは外部キーに制限があることに注意
-- PostgreSQL 11以降で改善されているが、完全ではない

-- 代替案：トリガーを使った制約の実装
CREATE OR REPLACE FUNCTION check_department_exists()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM departments 
        WHERE department_id = NEW.department_id
    ) THEN
        RAISE EXCEPTION 'Department % does not exist', NEW.department_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_department_trigger
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_department_exists();

ベストプラクティス

1. 命名規則を統一する

-- 良い例：分かりやすい命名
CONSTRAINT fk_employees_department
FOREIGN KEY (department_id) 
REFERENCES departments(department_id);

-- 複数の外部キーがある場合
CONSTRAINT fk_orders_user
CONSTRAINT fk_orders_shipping_address

2. 適切なカスケードオプションを選ぶ

-- 論理的な親子関係 → CASCADE
-- 注文と注文明細
ON DELETE CASCADE

-- 参照関係 → SET NULL or RESTRICT  
-- 商品とカテゴリー
ON DELETE SET NULL

-- 重要なマスターデータ → RESTRICT
-- ユーザーと注文履歴
ON DELETE RESTRICT

3. ドキュメント化する

-- テーブルとカラムにコメントを追加
COMMENT ON CONSTRAINT fk_employees_department ON employees 
IS '従業員が所属する部門への参照。部門削除時は従業員も削除される。';

COMMENT ON COLUMN employees.department_id 
IS '所属部門ID（departments.department_idを参照）';

4. テスト環境で検証する

-- テストデータで制約の動作を確認
BEGIN;

-- テストデータ作成
INSERT INTO departments (department_name) VALUES ('営業部');
INSERT INTO employees (employee_name, department_id) 
VALUES ('テスト社員', currval('departments_department_id_seq'));

-- カスケード削除のテスト
DELETE FROM departments WHERE department_name = '営業部';

-- 結果確認後、ロールバック
ROLLBACK;

複雑な外部キー設定

複合外部キー

複数の列の組み合わせで外部キーを構成する場合：

-- 在庫テーブル（倉庫IDと商品IDの組み合わせ）
CREATE TABLE inventory (
    warehouse_id INTEGER,
    product_id INTEGER,
    quantity INTEGER DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (warehouse_id, product_id)
);

-- 在庫移動履歴
CREATE TABLE inventory_movements (
    movement_id SERIAL PRIMARY KEY,
    from_warehouse_id INTEGER,
    from_product_id INTEGER,
    to_warehouse_id INTEGER,
    to_product_id INTEGER,
    quantity INTEGER NOT NULL,
    -- 複合外部キー（FROM側）
    CONSTRAINT fk_from_inventory
        FOREIGN KEY (from_warehouse_id, from_product_id)
        REFERENCES inventory(warehouse_id, product_id),
    -- 複合外部キー（TO側）
    CONSTRAINT fk_to_inventory
        FOREIGN KEY (to_warehouse_id, to_product_id)
        REFERENCES inventory(warehouse_id, product_id)
);

自己参照外部キー

同じテーブル内での参照関係：

-- 組織階層（上司と部下の関係）
CREATE TABLE organization (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id INTEGER,
    -- 自己参照
    CONSTRAINT fk_manager
        FOREIGN KEY (manager_id)
        REFERENCES organization(employee_id)
        ON DELETE SET NULL
);

-- カテゴリーの階層構造
CREATE TABLE category_tree (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    parent_id INTEGER,
    level INTEGER DEFAULT 0,
    path TEXT,  -- '1/5/12' のような階層パス
    CONSTRAINT fk_parent
        FOREIGN KEY (parent_id)
        REFERENCES category_tree(category_id)
        ON DELETE CASCADE
);

まとめ：外部キーで堅牢なデータベースを作ろう！

PostgreSQLの外部キーについて、基本から応用まで解説してきました。

重要なポイント：

  1. 外部キーはデータの整合性を守る強力な仕組み
  2. 適切なカスケードオプションを選ぶことが大切
  3. 既存データの整合性を確認してから追加する
  4. パフォーマンスを考慮してインデックスも設定
  5. 命名規則を統一して管理しやすくする

外部キーは最初は面倒に感じるかもしれません。でも、一度設定してしまえば、データベースが自動的にデータの整合性を守ってくれます。

アプリケーションのバグでおかしなデータが入ることを防ぎ、メンテナンスも楽になります。

次のステップ：

  • まずは小規模なテストDBで練習
  • 既存のDBに段階的に外部キーを追加
  • チーム内でルールを共有

正しく外部キーを使いこなせば、安心して運用できるデータベースが作れます。ぜひ、この記事を参考に、あなたのPostgreSQLデータベースをより堅牢なものにしてください！

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました