データベース設計の実践：外部制約の適用

今回は前回の外部制約の記事について、外部制約の設定を実際にPostgreSQLを使用してどのように動作するか見ていきましょう。

システムの概要
外部キー制約の設計と実装
1. 1. 外部キー制約のオプション
2. 2. 外部キー制約の設定
実際に試してみる
まとめ

システムの概要

前提として以下の2つのマスタテーブルをもとに説明していきます。

社員マスタ：各社員に一意の社員IDが割り当てられ、社員ごとに部署コードが関連付けられています。
部署マスタ：各部署に一意の部署コードが割り当てられ、部署名が定義されています。

制約のルールは次の通りです：

社員マスタに設定される部署コードは、必ず部署マスタに存在するものでなければなりません。
社員マスタに設定されている部署コードを持つ部署は、部署マスタから削除できません。

これを実現するために、外部キー制約を設定し、参照整合性を確保します。

外部キー制約の設計と実装

1. 外部キー制約のオプション

PostgreSQLの外部キー制約には、親テーブルの行が削除または更新されたときに子テーブルに対してどのような動作を行うかを制御するオプションがあります。以下はそのオプションと動作の説明です。

オプション	DDL例	説明
CASCADE	`ON DELETE CASCADE` `ON UPDATE CASCADE`	親テーブルの行が削除/更新された場合、対応する子テーブルの行も自動的に削除/更新されます。
RESTRICT	`ON DELETE RESTRICT` `ON UPDATE RESTRICT`	親テーブルの行が子テーブルに関連付けられている場合、その親テーブルの行を削除/更新することを禁止します。
NO ACTION	`ON DELETE NO ACTION` `ON UPDATE NO ACTION`	親テーブルの行が削除/更新されても、特に何も行わず、削除/更新を遅延させます（最終的にはRESTRICTと同じ動作）
SET NULL	`ON DELETE SET NULL` `ON UPDATE SET NULL`	親テーブルの行が削除/更新された場合、子テーブルの外部キー列をNULLに設定します。子テーブルの外部キー列がNULLを許容する必要があります。
SET DEFAULT	`ON DELETE SET DEFAULT` `ON UPDATE SET DEFAULT`	親テーブルの行が削除/更新された場合、子テーブルの外部キー列をデフォルト値に設定します。子テーブルの外部キー列がデフォルト値を持つ必要があります。

2. 外部キー制約の設定

今回は社員マスタに存在する部署コードが部署マスタから削除されることを防ぐために、RESTRICTの設定をします。

まず、部署マスタと社員マスタのテーブルを作成し、社員マスタの「部署コード」フィールドに外部キー制約を設定します。今回は部署マスタで部署コードの削除操作を行う際に、参照整合性を確保するためのオプションを設定するために「ON DELETE RESTRICT」を例にして説明します。

部署マスタのDDL

CREATE TABLE department_master (
    department_code VARCHAR(10) PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

社員マスタのDDL

CREATE TABLE employee_master (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(50) NOT NULL,
    department_code VARCHAR(10),
    CONSTRAINT fk_department
        FOREIGN KEY(department_code) 
        REFERENCES department_master(department_code)
        ON DELETE RESTRICT
);

実際に試してみる

それでは、実際にいくつかのデータを挿入して動作を確認してみましょう。

データの挿入

-- 部署マスタにデータを挿入
INSERT INTO department_master (department_code, department_name) VALUES ('D001', '営業部');
INSERT INTO department_master (department_code, department_name) VALUES ('D002', '技術部');

-- 社員マスタにデータを挿入
INSERT INTO employee_master (employee_name, department_code) VALUES ('佐藤', 'D001');
INSERT INTO employee_master (employee_name, department_code) VALUES ('鈴木', 'D002');

部署マスタから部署コードを削除してみる

DELETE FROM department_master WHERE department_code = 'D001';

この操作は失敗し、以下のようなエラーメッセージが表示されます：（DBeaver使用）

SQLエラー [23503]: ERROR: テーブル"department_master"の更新または削除は、テーブル"employee_master"の外部キー制約"fk_department"に違反します
  詳細: キー(department_code)=(D001)はまだテーブル"employee_master"から参照されています

このようにして、外部キー制約と参照整合性を適用することで、データの整合性を確保できます。

まとめ

外部制約を適用することで、データベースの整合性を強化し、誤ったデータの入力や削除を防ぐことができます。今回の例では、PostgreSQLを使用して企業の人事システムにおける社員マスタと部署マスタの外部制約を付与してどのような動きをするのか試してみました。

ぜひ皆さんも、実際に外部制約を試してみてください。