PostgreSQLの制約入門（PRIMARY KEY / UNIQUE / CHECK / FOREIGN KEY）

フォームのバリデーションだけでは、別サービスからの書き込みや手作業 SQL、同時実行まで含めた整合性は守り切れません。この記事では PRIMARY KEY / UNIQUE / CHECK / FOREIGN KEY を、小さな customers / orders スキーマで 1 つずつ破ってみながら、DB で何を守るべきか整理します。

先に整理すると:

• PRIMARY KEY / UNIQUE / CHECK / FOREIGN KEY の 4 制約を、実際に違反させながら psql のエラーで確認する
• アプリ側バリデーションと DB 制約の役割分担の考え方を整理する
• 環境は Docker + PostgreSQL 17 + psql。ORM やマイグレーションツールは対象外

特定のプログラミング言語や ORM は対象外です。再現範囲は Docker + PostgreSQL + psql に絞ります。

前提環境

• Windows 11
• WSL2（Ubuntu）
• VS Code（Remote - WSL）
• Docker Desktop
• PostgreSQL 17
• psql

以降のコマンド実行場所は、特記がない限り WSL 側ターミナルです。

1. ゴールと非対象

到達する状態:

• PRIMARY KEY が「行の識別子」であり、UNIQUE とは役割が少し違うと分かる
• UNIQUE / CHECK / FOREIGN KEY がどの不整合を止めるかを psql のエラーで確認できる
• アプリ側のバリデーションと DB 制約を、どちらか片方ではなく役割分担で考えられる

扱わない内容:

• NOT NULL の体系的な説明
• 複合主キーや複合 UNIQUE
• partial unique index や expression index
• ON DELETE CASCADE / RESTRICT / SET NULL
• DEFERRABLE / NOT VALID
• ORM やマイグレーションツールの書き方

今回は 4 種類の制約に集中します。NOT NULL は補助で使いますが、主題はそこではありません。

2. 最小デモ環境を作る

まず用意するのは、記事用の空ディレクトリです。

mkdir -p ~/projects/postgresql-constraints-intro-demo
cd ~/projects/postgresql-constraints-intro-demo
mkdir -p sql
code .

最終的な構成は、次の 4 ファイルで収まります。

postgresql-constraints-intro-demo/
├─ compose.yml
├─ .env.example
└─ sql/
   ├─ 01-schema.sql
   └─ 02-seed.sql

compose.yml は次の内容です。

services:
  db:
    image: postgres:17
    working_dir: /workspace
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data
      - ./:/workspace
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      interval: 5s
      timeout: 3s
      retries: 20

volumes:
  db-data:

今回もアプリケーションコンテナは置きません。制約の確認だけに集中するためです。

.env.example は次の内容で作成します。

POSTGRES_DB=app
POSTGRES_USER=app
POSTGRES_PASSWORD=app

起動は次のコマンドです。

cp .env.example .env
docker compose up -d
docker compose exec db pg_isready -U app -d app
docker compose exec db psql -U app -d app -c "SELECT version();"

確認ポイント:

• pg_isready が accepting connections を返す
• SELECT version(); が 1 行返る

詰まったとき:

docker compose logs db
docker compose down -v

3. 制約を Mermaid で先に俯瞰する

制約を 4 つばらばらに覚えるより、「どの書き込み経路も最後は DB を通る」と見たほうが整理しやすくなります。

flowchart LR
    A["Webフォーム"] --> D["INSERT / UPDATE"]
    B["バッチ処理"] --> D
    C["手作業 SQL"] --> D
    D --> E{"PostgreSQL の制約"}
    E -->|PRIMARY KEY| F["行を一意に識別する"]
    E -->|UNIQUE| G["業務識別子の重複を止める"]
    E -->|CHECK| H["不正な値や状態を止める"]
    E -->|FOREIGN KEY| I["存在しない親参照を止める"]

今回使うテーブル関係は次の 2 表です。

erDiagram
    CUSTOMERS ||--o{ ORDERS : "customer_id"

    CUSTOMERS {
        bigint id PK
        text email UK
        text customer_name
    }
    ORDERS {
        bigint id PK
        text order_number UK
        bigint customer_id FK
        text status "CHECK"
        integer total_amount "CHECK"
    }

4 種類の役割を 1 行ずつに戻すと、次のようになります。

• PRIMARY KEY: 各行を識別する軸。NULL も重複も許さない
• UNIQUE: order_number やメールアドレスのような重複禁止ルールを表す
• CHECK: status の許可値や total_amount >= 0 のような条件を表す
• FOREIGN KEY: orders.customer_id が実在する customers.id だけを参照できるようにする

アプリ側のバリデーションと DB 制約は、どちらか片方を選ぶ話ではありません。アプリ側は早く分かりやすく伝え、DB 側はすべての書き込み経路を最後に守る役目です。

4. スキーマと seed を作る

sql/01-schema.sql は次の内容で作成します。

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  customer_name TEXT NOT NULL
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_number TEXT NOT NULL UNIQUE,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  status TEXT NOT NULL CHECK (status IN ('draft', 'confirmed', 'shipped')),
  total_amount INTEGER NOT NULL CHECK (total_amount >= 0)
);

着眼点は次の 4 つあります。

• customers.id と orders.id は PRIMARY KEY
• customers.email と orders.order_number は UNIQUE
• orders.status と orders.total_amount は CHECK
• orders.customer_id は FOREIGN KEY

sql/02-seed.sql は次の内容で作成します。

TRUNCATE TABLE orders, customers RESTART IDENTITY CASCADE;

INSERT INTO customers (email, customer_name)
VALUES
  ('sato@example.com', '佐藤商店'),
  ('aoyama@example.com', '青山カフェ');

INSERT INTO orders (order_number, customer_id, status, total_amount)
VALUES
  ('ORD-1001', 1, 'confirmed', 3200),
  ('ORD-1002', 1, 'shipped', 6800),
  ('ORD-1003', 2, 'draft', 42000);

TRUNCATE ... RESTART IDENTITY CASCADE を入れてあるので、各章の前に同じ初期状態へ戻せる構成です。

スキーマと seed を適用します。

docker compose exec db psql -U app -d app -f sql/01-schema.sql
docker compose exec db psql -U app -d app -f sql/02-seed.sql

続いて \d で制約を確認します。

docker compose exec db psql -U app -d app -c "\\d customers"
docker compose exec db psql -U app -d app -c "\\d orders"

ここで見たい点は次のとおりです。

• customers に PRIMARY KEY (id) と UNIQUE (email) がある
• orders に PRIMARY KEY (id) と UNIQUE (order_number) がある
• orders に CHECK が 2 つある
• orders.customer_id が customers(id) を参照している

コードのポイント

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  customer_name TEXT NOT NULL
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_number TEXT NOT NULL UNIQUE,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  status TEXT NOT NULL CHECK (status IN ('draft', 'confirmed', 'shipped')),
  total_amount INTEGER NOT NULL CHECK (total_amount >= 0)
);

• 2行目 GENERATED ALWAYS AS IDENTITY PRIMARY KEY — GENERATED ALWAYS は手動で id を上書きしにくくする設定。PRIMARY KEY は UNIQUE + NOT NULL を暗黙で含む。
• 3行目 email TEXT NOT NULL UNIQUE — 列定義に直接書くインライン記法で UNIQUE 制約を追加。一意インデックスとして機能し、メールアドレスの重複を全書き込み経路で防ぐ。
• 10行目 REFERENCES customers(id) — 外部キー制約の宣言。customers.id に存在しない値を orders.customer_id へ入れようとすると即時エラーになる。
• 11行目 CHECK (status IN (...)) — 許可する状態値を列挙した制約。アプリが介在しないバッチ処理や手作業 SQL でも無効な状態値を拒否する。

5. PRIMARY KEY を確認する

最初に seed を流し直します。

docker compose exec db psql -U app -d app -f sql/02-seed.sql

現在の customers は次の 2 行です。

SELECT id, email, customer_name
FROM customers
ORDER BY id;

docker compose exec db psql -U app -d app -c "
SELECT id, email, customer_name
FROM customers
ORDER BY id;"

期待する結果:

id	email	customer_name
1	sato@example.com	佐藤商店
2	aoyama@example.com	青山カフェ

通常は id を自分で入れませんが、制約をわざと破るために OVERRIDING SYSTEM VALUE を付けて重複 id を挿入します。

INSERT INTO customers (id, email, customer_name)
OVERRIDING SYSTEM VALUE
VALUES (1, 'duplicate-id@example.com', '重複ID顧客');

docker compose exec db psql -U app -d app -c "
INSERT INTO customers (id, email, customer_name)
OVERRIDING SYSTEM VALUE
VALUES (1, 'duplicate-id@example.com', '重複ID顧客');"

返るのは次のエラーです。

ERROR:  duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (id)=(1) already exists.

PRIMARY KEY は単に「重複禁止の列」ではありません。行を一意に識別する軸なので、UNIQUE よりも基礎にある制約です。
email や order_number は業務上の識別子として変わる可能性がありますが、id は行そのものを指し続ける安定したキーとして置きます。

6. UNIQUE を確認する

ここでも seed を流し直します。

docker compose exec db psql -U app -d app -f sql/02-seed.sql

orders の現在値は次のとおりです。

SELECT id, order_number, customer_id, status
FROM orders
ORDER BY id;

docker compose exec db psql -U app -d app -c "
SELECT id, order_number, customer_id, status
FROM orders
ORDER BY id;"

期待する結果:

id	order_number	customer_id	status
1	ORD-1001	1	confirmed
2	ORD-1002	1	shipped
3	ORD-1003	2	draft

重複する注文番号を追加します。

INSERT INTO orders (order_number, customer_id, status, total_amount)
VALUES ('ORD-1001', 2, 'confirmed', 5000);

docker compose exec db psql -U app -d app -c "
INSERT INTO orders (order_number, customer_id, status, total_amount)
VALUES ('ORD-1001', 2, 'confirmed', 5000);"

結果は次のエラーです。

ERROR:  duplicate key value violates unique constraint "orders_order_number_key"
DETAIL:  Key (order_number)=(ORD-1001) already exists.

UNIQUE は、業務上の「重なってはいけない値」を DB 側で止める制約です。
アプリ側で「同じ注文番号がないか」を先に確認していても、同時実行や別経路の書き込みではすり抜ける可能性があります。最後に重複を止めるのは DB 側の UNIQUE です。

7. CHECK を確認する

再現をそろえるため、もう一度 seed を流します。

docker compose exec db psql -U app -d app -f sql/02-seed.sql

CHECK は「この行はこの条件を満たしていなければ保存しない」というルールです。今回は 2 つの失敗例を見ます。

許可していない status を入れます。

INSERT INTO orders (order_number, customer_id, status, total_amount)
VALUES ('ORD-1099', 1, 'paid', 5000);

docker compose exec db psql -U app -d app -c "
INSERT INTO orders (order_number, customer_id, status, total_amount)
VALUES ('ORD-1099', 1, 'paid', 5000);"

結果は次のエラーです。

ERROR:  new row for relation "orders" violates check constraint "orders_status_check"
DETAIL:  Failing row contains (4, ORD-1099, 1, paid, 5000).

次は、既存注文の total_amount を負の値へ更新する例です。

UPDATE orders
SET total_amount = -500
WHERE id = 1;

docker compose exec db psql -U app -d app -c "
UPDATE orders
SET total_amount = -500
WHERE id = 1;"

こちらも拒否されます。

ERROR:  new row for relation "orders" violates check constraint "orders_total_amount_check"
DETAIL:  Failing row contains (1, ORD-1001, 1, confirmed, -500).

CHECK が効くのは INSERT だけではありません。UPDATE で条件を壊しても保存不可です。
つまり、CHECK は行単位の真偽条件です。status IN ('draft', 'confirmed', 'shipped') や total_amount >= 0 のように、その場で判定できる条件を DB 側へ置くと、不正な状態を入口に依存せず止められます。

8. FOREIGN KEY を確認する

ここでも seed を流し直します。

docker compose exec db psql -U app -d app -f sql/02-seed.sql

まず確認するのは、現在の注文と顧客の対応です。

SELECT
  o.order_number,
  c.customer_name,
  o.status,
  o.total_amount
FROM orders AS o
JOIN customers AS c
  ON c.id = o.customer_id
ORDER BY o.id;

docker compose exec db psql -U app -d app -c "
SELECT
  o.order_number,
  c.customer_name,
  o.status,
  o.total_amount
FROM orders AS o
JOIN customers AS c
  ON c.id = o.customer_id
ORDER BY o.id;"

期待する結果:

order_number	customer_name	status	total_amount
ORD-1001	佐藤商店	confirmed	3200
ORD-1002	佐藤商店	shipped	6800
ORD-1003	青山カフェ	draft	42000

次に、存在しない顧客 ID を参照する注文を追加します。

INSERT INTO orders (order_number, customer_id, status, total_amount)
VALUES ('ORD-2001', 999, 'confirmed', 1200);

docker compose exec db psql -U app -d app -c "
INSERT INTO orders (order_number, customer_id, status, total_amount)
VALUES ('ORD-2001', 999, 'confirmed', 1200);"

結果は次のエラーです。

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

FOREIGN KEY が守っているのは「関係があるらしい値」ではなく、「本当に親表に存在するキーだけを参照させること」です。
外部キーの削除時挙動までは今回は切り分けます。そこを深めるなら、後続の PostgreSQLの外部キーと削除ルールを整理する（CASCADE / RESTRICT / SET NULL） が近道です。

9. どの層で何を守るかを整理する

アプリ側のバリデーションを消して DB 制約だけに寄せる、という話ではありません。役割は別です。

守りたいこと	アプリ側でやる理由	DB 側でやる理由	今回の例
行を識別すること	URL や画面遷移で対象行を扱いやすくする	行の一意性を全経路で守る	PRIMARY KEY
注文番号やメールの重複防止	送信前に「その番号は使用済み」と伝えられる	同時実行や別サービスの書き込みでも重複を止める	UNIQUE
不正な状態や負の金額の防止	入力中に候補やメッセージを出せる	バッチ処理や手作業 SQL でも不正値を保存させない	CHECK
実在しない顧客参照の防止	UI 上で候補選択を絞り込める	参照切れデータを保存させない	FOREIGN KEY

「そのルールを、すべての書き込み経路で必ず守りたいか」を自問して、答えが yes なら DB 制約へ置く価値があります。早いフィードバックや丁寧なエラーメッセージはアプリ側が得意ですが、最後の保存可否を決めるのは DB 側です。

10. まとめ

今回確認したことは次の 4 つです。

• PRIMARY KEY は行そのものを識別する軸
• UNIQUE は業務識別子の重複を防ぐ
• CHECK はその行で許されない値や状態を止める
• FOREIGN KEY は存在しない親参照を止める

PRIMARY KEY と UNIQUE はどちらも重複を許しませんが、前者は行の土台、後者は業務ルールという違いがあります。ここが見えると、制約をただ足すのではなく役割で置き分けやすくなります。

次の一歩として、テーブル関係の作り方から見直したいなら PostgreSQLでER図からテーブル設計する（Mermaid + CREATE TABLE最小構成）、外部キーの削除挙動まで整理したいなら PostgreSQLの外部キーと削除ルールを整理する（CASCADE / RESTRICT / SET NULL） を読むと流れをつかみやすくなります。