PostgreSQL: PostgreSQL制約とデータ整合性

最終更新：2026-08-26

1. 学習内容

PRIMARY KEY（単一列 / 複合）
FOREIGN KEY（CASCADE / SET NULL / SET DEFAULT / NO ACTION / RESTRICT）
UNIQUE制約
NOT NULL制約
CHECK制約（PostgreSQLの機能：同じ行の他の列を参照可能）
EXCLUSION制約（PostgreSQLの機能：例：重複しない時間範囲）
DEFERRABLE遅延制約（PostgreSQLの機能）
制約の命名と管理

2. ストーリー

CharlieはSaaSプラットフォームのデータベースアーキテクトです。彼は以下のビジネスルールをデータベースレベルで強制する必要があります：

すべての注文金額は0より大きくなければならない（CHECK）
会議室予約の時間範囲は重複してはならない（EXCLUSION）
顧客を削除するとその注文も自動的にクリーンアップされるが、重要な注文は保護される（FOREIGN KEYカスケード戦略）
データの一括インポート時、行間の参照が一時的に制約に違反する可能性があり、インポート完了後にのみチェックされるべき（DEFERRABLE）

Charlieはアプリケーション層のバリデーション��代わりに制約を使用し、データベースをデータ整合性の最後の防衛線とします。

3. 概念：PRIMARY KEY

(1) 主キーの役割

主キーはテーブル内の各行を一意に識別し、UNIQUE + NOT NULLを組み合わせたものです。テーブルには1つの主キーのみ設定できます。

特性	説明
一意性	重複した値は許可されない
NULL不可	NULLは許可されない
自動インデックス	PostgreSQLは主キーに対して自動的にB-Tree一意インデックスを作成
テーブルごとに1つ	定義できるPRIMARY KEYは1つのみ

▶ サンプル：単一列主キー

SQL

CREATE TABLE customers (
  customer_id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE
);

TEXT 📖 参照専用

CREATE TABLE

▶ サンプル：複合主キー

SQL

CREATE TABLE order_items (
  order_id   BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity   INT NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

TEXT 📖 参照専用

CREATE TABLE

(2) 主キー列の型比較

型	ストレージ	範囲	ユースケース
SERIAL / BIGSERIAL	4/8バイト	2B / 9.2×10¹⁸	ほとんどのビジネステーブル
UUID	16バイト	グローバルに一意	分散システム
自然キー（例：email）	可変	—	稀に使用、ビジネス変更リスクあり
複合主キー	複数列	—	ジャンクションテーブル、多対多ブリッジテーブル

▶ サンプル：UUID主キー

SQL

CREATE TABLE global_events (
  event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_name VARCHAR(200) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

TEXT 📖 参照専用

CREATE TABLE

4. 概念：FOREIGN KEY

(1) 外部キーと5つのカスケード戦略

外部キーは参照整合性を保証します：子テーブルの参照列の値は、親テーブルの主キー/一意キーに存在する必要があります。

戦略	ON DELETE動作	ON UPDATE動作	典型的なシナリオ
CASCADE	子行をカスケード削除	子行をカスケード更新	注文とともに注文明細を削除
SET NULL	子行をNULLに設定	子行をNULLに設定	オプションの関連付け、削除の影響なし
SET DEFAULT	子行をデフォルト値に設定	子行をデフォルト値に設定	稀に使用
RESTRICT	削除を拒否（即時）	更新を拒否（即時）	重要データの保護
NO ACTION	削除を拒否（遅延可能）	更新を拒否（遅延可能）	デフォルトの動作

▶ サンプル：CASCADEカスケード削除

SQL

CREATE TABLE orders (
  order_id    BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL
    REFERENCES customers(customer_id) ON DELETE CASCADE,
  total_amount DECIMAL(12,2) NOT NULL
);

TEXT 📖 参照専用

CREATE TABLE

顧客が削除されると、そのすべての注文が自動的に削除されます。

▶ サンプル：RESTRICTで重要データを保護

SQL

CREATE TABLE invoices (
  invoice_id  BIGSERIAL PRIMARY KEY,
  order_id    BIGINT NOT NULL
    REFERENCES orders(order_id) ON DELETE RESTRICT,
  invoice_date DATE NOT NULL
);

TEXT 📖 参照専用

CREATE TABLE

注文に既に請求書がある場合、その注文の削除は拒否されます。

(2) CASCADE vs RESTRICTの比較

観点	CASCADE	RESTRICT
親行の削除	子行も削除される	削除が拒否される
データ安全性	便利だがリスクあり	安全だが手動クリーンアップが必要
ユースケース	付随データ	重要 / 財務データ

▶ サンプル：多段カスケード

SQL

CREATE TABLE customers (
  customer_id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id    BIGSERIAL PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(customer_id) ON DELETE CASCADE
);

CREATE TABLE order_items (
  item_id     BIGSERIAL PRIMARY KEY,
  order_id    BIGINT REFERENCES orders(order_id) ON DELETE CASCADE,
  product_id  BIGINT NOT NULL
);

TEXT 📖 参照専用

CREATE TABLE

顧客削除 → 注文を自動削除 → order_itemsを自動削除、3段階のカスケードです。

▶ サンプル：SET NULL

SQL

CREATE TABLE reviews (
  review_id   BIGSERIAL PRIMARY KEY,
  product_id  BIGINT REFERENCES products(product_id) ON DELETE SET NULL,
  content     TEXT NOT NULL
);

TEXT 📖 参照専用

CREATE TABLE

商品が削除された後、レビューのproduct_idはNULLに設定されますが、レビュー自体は保持されます。

5. 概念：UNIQUEとNOT NULL

(1) UNIQUE制約

UNIQUEは列の値（または列の組み合わせ）が重複しないことを保証し、NULLを許可します（複数のNULLは重複としてカウントされません）。

観点	PRIMARY KEY	UNIQUE
テーブルあたりの数	1つのみ	複数可
NULL許可	不可	可（複数のNULLは競合しない）
自動インデックス	あり	あり
意味	行を識別	一意性を保証

▶ サンプル：複数列UNIQUE制約

SQL

CREATE TABLE user_accounts (
  user_id BIGSERIAL PRIMARY KEY,
  email   VARCHAR(255) UNIQUE,
  phone   VARCHAR(20),
  UNIQUE (email, phone)
);

TEXT 📖 参照専用

CREATE TABLE

email単独で一意＋(email, phone)の組み合わせで一意。

(2) NOT NULL制約

NOT NULLは列にNULL値を格納することを禁止し、最も基本的な整合性制約です。

記述方法	説明
`col TYPE NOT NULL`	列レベルの制約
`CONSTRAINT nn_col CHECK (col IS NOT NULL)`	同等の形式

▶ サンプル：NOT NULLの組み合わせ

SQL

CREATE TABLE products (
  product_id   BIGSERIAL PRIMARY KEY,
  name         VARCHAR(200) NOT NULL,
  unit_price   DECIMAL(10,2) NOT NULL,
  description  TEXT
);

TEXT 📖 参照専用

CREATE TABLE

descriptionはNULLを許可し、他のキー列は許可しません。

6. 概念：CHECK制約

(1) CHECK制約の基本

CHECK制約は列の値が指定されたブーリアン式を満たすことを要求します。PostgreSQLのCHECKは同じ行の他の列を参照できます（SQL標準もサポートしていますが、多くのデータベースはサポートしていません）。

特性	説明
行レベルのチェック	現在の行の列のみ参照可能
PostgreSQLの機能	他の行を参照可能（サブクエリ経由、制限あり）
テーブルレベルのCHECK	複数列を同時に制約可能
NO INHERIT	子テーブルに伝播しない（PGの機能）

▶ サンプル：金額は正の値でなければならない

SQL

CREATE TABLE orders (
  order_id    BIGSERIAL PRIMARY KEY,
  total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount > 0),
  discount    DECIMAL(12,2) DEFAULT 0 CHECK (discount >= 0)
);

TEXT 📖 参照専用

CREATE TABLE

▶ サンプル：複数列CHECK制約

SQL

CREATE TABLE campaigns (
  campaign_id BIGSERIAL PRIMARY KEY,
  start_date  DATE NOT NULL,
  end_date    DATE NOT NULL,
  budget      DECIMAL(12,2) NOT NULL CHECK (budget > 0),
  CONSTRAINT chk_date_range CHECK (end_date >= start_date),
  CONSTRAINT chk_budget_limit CHECK (budget <= 1000000)
);

TEXT 📖 参照専用

CREATE TABLE

(2) 一般的なCHECKパターン

ビジネスルール	CHECK式
金額が正	`CHECK (amount > 0)`
割引範囲	`CHECK (discount BETWEEN 0 AND 1)`
日付順序	`CHECK (end_date >= start_date)`
列挙値	`CHECK (status IN ('active', 'inactive', 'pending'))`
文字列長	`CHECK (LENGTH(phone) >= 10)`
パーセンテージ	`CHECK (rate >= 0 AND rate <= 100)`

▶ サンプル：管理しやすい名前付き制約

SQL

CREATE TABLE subscriptions (
  sub_id    BIGSERIAL PRIMARY KEY,
  plan      VARCHAR(50) NOT NULL,
  price     DECIMAL(10,2) NOT NULL,
  CONSTRAINT chk_price_positive CHECK (price > 0),
  CONSTRAINT chk_plan_valid CHECK (plan IN ('free', 'basic', 'pro', 'enterprise'))
);

TEXT 📖 参照専用

CREATE TABLE

▶ サンプル：既存テーブルにCHECKを追加

SQL

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive CHECK (total_amount > 0);

TEXT 📖 参照専用

-- SQL文が正常に実行されました

7. 概念：EXCLUSION制約

(1) EXCLUSIONの仕組み

EXCLUSION制約は以下を保証します：2つの行が指定された列で「等しい」場合（=演算子で比較）、指定された次元で「重複」しない（重複演算子で比較）。これはPostgreSQL固有の機能です。

シナリオ	制約	演算子
時間範囲が重複しない	同じリソースの時間が交差しない	`=, &&`
座席の重複なし	同じセッションの座席番号が重複しない	`=, =`（UNIQUEと同等）

▶ サンプル：会議室予約の時間重複禁止

SQL

CREATE TABLE room_bookings (
  booking_id BIGSERIAL PRIMARY KEY,
  room_id    INT NOT NULL,
  time_range TSTZRANGE NOT NULL,
  booked_by  VARCHAR(100) NOT NULL,
  CONSTRAINT excl_room_no_overlap
    EXCLUDE USING GiST (room_id WITH =, time_range WITH &&)
);

TEXT 📖 参照専用

CREATE TABLE

▶ サンプル：競合する挿入

SQL

INSERT INTO room_bookings (room_id, time_range, booked_by)
VALUES (1, '[2025-07-13 09:00, 2025-07-13 11:00)', 'Alice');

INSERT INTO room_bookings (room_id, time_range, booked_by)
VALUES (1, '[2025-07-13 10:00, 2025-07-13 12:00)', 'Bob');

TEXT 📖 参照専用

ERROR: conflicting key value violates exclusion constraint "excl_room_no_overlap"
DETAIL: Key (room_id, time_range)=(1, [2025-07-13 10:00,2025-07-13 12:00))
conflicts with existing key (room_id, time_range)=(1, [2025-07-13 09:00,2025-07-13 11:00)).

(2) EXCLUSION vs UNIQUEの比較

観点	UNIQUE	EXCLUSION
比較演算子	`=`のみ	カスタム（`=`、`&&`、`<->`など）
範囲重複	非サポート	サポート
インデックス型	B-Tree	GiST / SP-GiST
柔軟性	低い	高い
ユースケース	離散値の一意性	重複しない範囲、距離制約

▶ サンプル：価格範囲が重複しない

SQL

CREATE TABLE discount_tiers (
  tier_id    BIGSERIAL PRIMARY KEY,
  product_id INT NOT NULL,
  price_range NUMRANGE NOT NULL,
  discount   DECIMAL(5,4) NOT NULL,
  CONSTRAINT excl_price_no_overlap
    EXCLUDE USING GiST (product_id WITH =, price_range WITH &&)
);

TEXT 📖 参照専用

CREATE TABLE

8. 概念：DEFERRABLE遅延制約

(1) 遅延制約のメカニズム

デフォルトでは、制約は各文の直後にチェックされます。DEFERRABLE制約は、チェックをトランザクションコミット時まで遅延させることができ、一括操作での循環参照問題を解決します。

モード	チェックタイミング	構文
IMMEDIATE	各文の後	デフォルトの動作
DEFERRABLE INITIALLY IMMEDIATE	各文の後（切り替え可能）	DEFERRABLE INITIALLY IMMEDIATE
DEFERRABLE INITIALLY DEFERRED	トランザクションコミット時	DEFERRABLE INITIALLY DEFERRED

▶ サンプル：循環参照の解決策

SQL

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  manager_id INT
);

ALTER TABLE departments
ADD CONSTRAINT fk_dept_manager
  FOREIGN KEY (manager_id) REFERENCES departments(dept_id)
  DEFERRABLE INITIALLY DEFERRED;

TEXT 📖 参照専用

CREATE TABLE

▶ サンプル：トランザクション内の操作

SQL

BEGIN;

INSERT INTO departments (dept_id, name, manager_id)
VALUES (1, 'Engineering', 2);

INSERT INTO departments (dept_id, name, manager_id)
VALUES (2, 'QA', 1);

COMMIT;

TEXT 📖 参照専用

INSERT 0 1

最初のINSERTだけを実行すると失敗しますが（manager_id=2がまだ存在しない）、DEFERRABLEによりチェックがCOMMITまで遅延されるため、両方のINSERTが完了すると検証が通ります。

(2) チェックタイミングの動的切り替え

▶ サンプル：SET CONSTRAINTS

SQL

BEGIN;
SET CONSTRAINTS fk_dept_manager DEFERRED;

INSERT INTO departments (dept_id, name, manager_id) VALUES (3, 'Sales', 4);
INSERT INTO departments (dept_id, name, manager_id) VALUES (4, 'Marketing', 3);

SET CONSTRAINTS fk_dept_manager IMMEDIATE;
COMMIT;

TEXT 📖 参照専用

INSERT 0 1

(3) IMMEDIATE vs DEFERREDの比較

観点	IMMEDIATE	DEFERRED
チェックタイミング	各文の後	トランザクションコミット時
パフォーマンス	高速（即時フィードバック）	やや遅い（バッチチェック）
循環参照	解決不可	解決可能
リスク	低い	違反がトランザクション終了時まで発見されない
ユースケース	ほとんどの制約	循環参照、一括インポート

9. 概念：制約の命名と管理

(1) 制約の命名規則

適切な命名は問題の特定と操作を容易にします。

制約タイプ	推奨プレフィックス	例
PRIMARY KEY	pk_	pk_orders
FOREIGN KEY	fk_	fk_orders_customer
UNIQUE	uq_	uq_users_email
CHECK	chk_	chk_amount_positive
EXCLUSION	excl_	excl_room_no_overlap

▶ サンプル：すべての制約を明示的に命名

SQL

CREATE TABLE orders (
  order_id     BIGSERIAL      CONSTRAINT pk_orders PRIMARY KEY,
  customer_id  BIGINT NOT NULL CONSTRAINT fk_orders_customer
    REFERENCES customers(customer_id) ON DELETE CASCADE,
  total_amount DECIMAL(12,2)  CONSTRAINT chk_amount_positive CHECK (total_amount > 0),
  status       VARCHAR(20)    CONSTRAINT chk_status_valid
    CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
  order_date   TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

TEXT 📖 参照専用

CREATE TABLE

(2) 制約管理操作

操作	構文
制約の追加	`ALTER TABLE t ADD CONSTRAINT name CHECK (...)`
制約の削除	`ALTER TABLE t DROP CONSTRAINT name`
制約の表示	`SELECT * FROM pg_constraint WHERE conrelid = 't'::regclass`
トリガーの無効化（間接的に制約を無効化）	`ALTER TABLE t DISABLE TRIGGER ALL`

▶ サンプル：テーブルの全制約を表示

SQL

SELECT
  conname AS constraint_name,
  contype AS type,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;

TEXT 📖 参照専用

 constraint_name       | type | definition
-----------------------+------+--------------------------------------------
 pk_orders             | p    | PRIMARY KEY (order_id)
 fk_orders_customer    | f    | FOREIGN KEY (customer_id) REFERENCES ...
 chk_amount_positive   | c    | CHECK ((total_amount > 0))
 chk_status_valid      | c    | CHECK ((status = ANY (ARRAY['pending'::...

10. 総合的な例

CharlieのSaaSプラットフ��ーム制約スキーム — 主キー、外部キーカスケード、CHECK、EXCLUSION、DEFERRABLEを網羅：

SQL

CREATE TABLE customers (
  customer_id BIGSERIAL    CONSTRAINT pk_customers PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) CONSTRAINT uq_customers_email UNIQUE,
  credit_limit DECIMAL(12,2) DEFAULT 0
    CONSTRAINT chk_credit_non_negative CHECK (credit_limit >= 0)
);

CREATE TABLE orders (
  order_id     BIGSERIAL      CONSTRAINT pk_orders PRIMARY KEY,
  customer_id  BIGINT NOT NULL CONSTRAINT fk_orders_customer
    REFERENCES customers(customer_id) ON DELETE RESTRICT,
  total_amount DECIMAL(12,2) NOT NULL
    CONSTRAINT chk_amount_positive CHECK (total_amount > 0),
  status       VARCHAR(20) DEFAULT 'pending'
    CONSTRAINT chk_status CHECK (status IN ('pending','shipped','delivered','cancelled')),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE room_bookings (
  booking_id BIGSERIAL   CONSTRAINT pk_bookings PRIMARY KEY,
  room_id    INT NOT NULL,
  time_range TSTZRANGE NOT NULL,
  booked_by  VARCHAR(100) NOT NULL,
  CONSTRAINT excl_room_no_overlap
    EXCLUDE USING GiST (room_id WITH =, time_range WITH &&)
);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_deferred
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE RESTRICT
  DEFERRABLE INITIALLY IMMEDIATE;

11. 実行フロー

DML文の実行における制約チェックの位置：

flowchart TD
    A[INSERT/UPDATE/DELETE] --> B{NOT NULLチェック}
    B -->|通過| C{CHECKチェック}
    B -->|失敗| Z[エラー、ロールバック]
    C -->|通過| D{UNIQUEチェック}
    C -->|失敗| Z
    D -->|通過| E{PRIMARY KEYチェック}
    D -->|失敗| Z
    E -->|通過| F{EXCLUSIONチェック}
    E -->|失敗| Z
    F -->|通過| G{制約タイミング？}
    F -->|失敗| Z
    G -->|IMMEDIATE| H[即時にFKチェック]
    G -->|DEFERRED| I[FKチェックをCOMMITまで遅延]
    H -->|通過| J[文の完了]
    I --> K[COMMIT]
    K --> L[全DEFERRED FKをチェック]
    L -->|通過| M[トランザクショ��コミット済み]
    L -->|失敗| Z

    style Z fill:#ffccbc
    style J fill:#c8e6c9
    style M fill:#c8e6c9

❓ よくある質問

Q RESTRICTとNO ACTIONの違いは何ですか？

A 機能的にはほぼ同じで、どちらも削除/更新を拒否します。違いは、NO ACTIONはDEFERRABLEと組み合わせてチェックを遅延できるのに対し、RESTRICTは常に即時チェックされる点です。

Q UNIQUE制約は複数のNULLを許可しますか？

A PostgreSQLでは、UNIQUE列は複数のNULLを許可します。NULL != NULLだからです。NULLも一意である必要がある場合は、NOT NULL制約を追加してください。

Q CHECK制約は他のテーブルの行を参照できますか？

A サブクエリを記述することはできますが、PostgreSQLのCHECKは現在の行の制約のみを保証し、行間の一貫性は保証しません（別のセッションが参照データを変更する可能性があります）。テーブル間の一貫性には外部キーを使用してください。

Q EXCLUSION制約にはどのインデックスが必要ですか？

A GiSTまたはSP-GiSTインデックスによるサポートが必要です。PostgreSQLはEXCLUSION制約用に対応するインデックスを自動的に作成します。

Q DEFERRABLEはCHECK制約に使用できますか？

A いいえ。DEFERRABLEはFOREIGN KEYとUNIQUE制約にのみ適用されます。CHECKとNOT NULLは常に即時チェックされます。

Q 大規模なCASCADE削除はパフォーマンスに影響しますか？

A はい。カスケード削除は行単位で実行され、大量のロックとI/Oを生成する可能性があります。大規模なバッチ削除では、長時間のトランザクションを避けるために、最初に子テーブルのデータを手動で削除してから親行を削除することを推奨します。

📖 まとめ

PRIMARY KEY = UNIQUE + NOT NULL、テーブルごとに1つ、自動的にインデックスを作成
FOREIGN KEYには5つのカスケード戦略がある：CASCADE / SET NULL / SET DEFAULT / RESTRICT / NO ACTION
CASCADEは便利だがリス��あり、RESTRICTは安全だが手動クリーンアップが必要
UNIQUEは複数のNULLを許可し、PRIMARY KEYはNULLを一切許可しない
CHECK制約は同じ行の他の列を参照でき、複数列の論理検証に最適
EXCLUSION制約（PGの機能）は範囲の重複禁止を保証（例：会議室予約）
DEFERRABLE遅延制約（PGの機能）は循環参照と一括インポート��問題を解決
制約を明示的に命名することで管理と運用が容易になり、pk_/fk_/uq_/chk_/excl_プレフィックスを推奨

📝 練習問題

⭐ customersテーブルとordersテーブルを作成し、主キーと外部キー（ON DELETE CASCADE）を定義し、注文金額が0より大きいことを保証するCHECK制約を追加してください。
⭐ SKU列がUNIQUEで、価格 > 0かつ割引 <= 価格を保証するCHECK制約を持つproductsテーブルを作成してください。
⭐⭐ EXCLUSION制約を使用してroom_bookingsテーブルを作成し、同じ会議室の時間範囲が重複しないことを保証し、競合する挿入をテストしてください。
⭐⭐ 相互に参照する2つのテーブル（employeesがdepartmentsを参照し、departmentsのmanager_idがemployeesを参照）を作成し、DEFERRABLEを使用して循環参照を解決してください。
⭐⭐⭐ 完全なSaaSテナントデータモデルを設計してください：テナントテーブル、ユーザーテーブル（外部キーカスケード）、サブスクリプションテーブル（日付と金額を検証するCHECK）、リソース予約テーブル（時間重複を防ぐEXCLUSION）、すべての制約に名前を付けてください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて