PostgreSQLで「特定の値だけ重複を許す」ユニーク制約（部分ユニークインデックス）

論理削除フラグを持つ表で「同じ値は1件だけ、ただし削除済みの行は同じ値が何件あってもよい」を成り立たせたい場面がある。素の UNIQUE は表のすべての行を対象にするので、この「一部の行だけ一意」は表せない。PostgreSQL では WHERE 付きのユニークインデックス（部分ユニークインデックス）で表す。

この記事では documents という小さな表を使い、素の UNIQUE で詰まるところから、複合 UNIQUE に張り替えたときにはまる NULL の落とし穴、そして部分ユニークインデックスでの解き方までを psql で順に確認する。

先に整理すると:

素の UNIQUE (slug) は削除済みの行も対象にするため、論理削除した値を新しい行で使い回せない
逃げ道に見える UNIQUE (slug, deleted_at) は、未削除行の deleted_at がすべて NULL で、PostgreSQL は既定で NULL 同士を重複と見なさない。狙った「未削除の重複」をすり抜ける
CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL なら、未削除の行だけに一意性がかかり、削除済みの行はいくつ同じ値があってもよい

特定のプログラミング言語や ORM は対象外とする。再現範囲は Docker + PostgreSQL + psql に絞る。制約の基本（PRIMARY KEY / UNIQUE / CHECK / FOREIGN KEY）は PostgreSQLの制約入門（PRIMARY KEY / UNIQUE / CHECK / FOREIGN KEY）にある。

前提環境

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

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

1. 何をしたいのか

題材は、記事や書類に一意な slug を付ける表とする。運用では物理削除ではなく、deleted_at に時刻を入れる論理削除を使う。守りたいルールは1つだ。

未削除（deleted_at IS NULL）の行の中では、slug は重複してはいけない
削除済み（deleted_at に時刻が入っている）の行は、同じ slug が何件あってもよい。過去に使った slug を新しい行で再利用したいから

「すべての行で一意」ではなく「ある条件を満たす行の中だけで一意」。この差が、素の UNIQUE と部分ユニークインデックスを分ける。

flowchart TD
    A["slug を INSERT / UPDATE"] --> B{"その slug の<br/>未削除行が既にある？"}
    B -->|ある| C["拒否したい"]
    B -->|ない| D["通したい<br/>（削除済みに同じ slug があっても）"]

2. 最小デモ環境を作る

記事用の空ディレクトリを用意する。

mkdir -p ~/projects/postgresql-partial-unique-index-demo
cd ~/projects/postgresql-partial-unique-index-demo
mkdir -p sql
code .

構成は次の3ファイルに収まる。

postgresql-partial-unique-index-demo/
├─ compose.yml
├─ .env.example
└─ sql/
   └─ 01-schema.sql

compose.yml を作成する。アプリケーションコンテナは置かず、DB の挙動だけを見る。

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();"

/var/run/postgresql:5432 - accepting connections
                                                      version
--------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.9 (Debian 17.9-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)

詰まったときは、ログを見てから作り直す。

docker compose logs db
docker compose down -v

3. 素の `UNIQUE` は「消したら再利用」ができない

最初に、いちばん素直な slug text NOT NULL UNIQUE を試す。この定義がなぜ足りないのかを先に見ておくと、部分インデックスが何を解いているかが分かりやすい。次の表を作成する。

docker compose exec db psql -U app -d app -c "
CREATE TABLE documents (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug       text NOT NULL UNIQUE,
  deleted_at timestamptz
);"

report-2024 を1件入れ、それを論理削除する。

docker compose exec db psql -U app -d app -c "
INSERT INTO documents (slug) VALUES ('report-2024');
UPDATE documents SET deleted_at = now() WHERE slug = 'report-2024';"

INSERT 0 1
UPDATE 1

削除済みになったので、同じ slug を新しい行で使いたい。だが INSERT は拒否される。

docker compose exec db psql -U app -d app -c "
INSERT INTO documents (slug) VALUES ('report-2024');"

ERROR:  duplicate key value violates unique constraint "documents_slug_key"
DETAIL:  Key (slug)=(report-2024) already exists.

素の UNIQUE は、行が論理削除されているかどうかを見ない。表に report-2024 が1行でも残っていれば、状態に関係なく重複と判断する。使い回しをしたい要件とは噛み合わない。

次の章に進む前に、表を捨てておく。

docker compose exec db psql -U app -d app -c "DROP TABLE documents;"

4. 複合 `UNIQUE (slug, deleted_at)` の落とし穴

「削除済みなら重複を許したい」と考えると、deleted_at を一意性の対象に混ぜる案が浮かぶ。UNIQUE (slug, deleted_at) にすれば、slug が同じでも deleted_at が違えば別扱いになり、再利用できそうに見える。試してみる。

docker compose exec db psql -U app -d app -c "
CREATE TABLE documents (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug       text NOT NULL,
  deleted_at timestamptz,
  UNIQUE (slug, deleted_at)
);"

削除済みの再利用は確かに通る。だがその前に、いちばん止めたかったはずの「未削除の重複」を試す。同じ slug を2件、どちらも未削除（deleted_at は既定の NULL）で入れる。

docker compose exec db psql -U app -d app -c "
INSERT INTO documents (slug) VALUES ('report-2024');
INSERT INTO documents (slug) VALUES ('report-2024');"

INSERT 0 1
INSERT 0 1

2件とも通ってしまう。未削除の report-2024 が2行できた。

docker compose exec db psql -U app -d app -c "
SELECT id, slug, deleted_at FROM documents WHERE slug = 'report-2024' ORDER BY id;"

 id |    slug     | deleted_at
----+-------------+------------
  1 | report-2024 |
  2 | report-2024 |
(2 rows)

原因は NULL の比較にある。PostgreSQL の UNIQUE は既定で NULL 同士を「異なる値」として扱う（NULLS DISTINCT）。未削除行の deleted_at はどれも NULL なので、(report-2024, NULL) と (report-2024, NULL) は制約から見ると別のキーになり、重複と判定されない。守りたかった側がすり抜け、要件と逆の結果になる。表を捨てて、正しい解き方に移る。

docker compose exec db psql -U app -d app -c "DROP TABLE documents;"

5. 部分ユニークインデックスで「未削除だけ一意」にする

一意性を「行全体」ではなく「未削除の行だけ」に限定する。これを表すのが WHERE 付きのユニークインデックスだ。sql/01-schema.sql を作成する。

CREATE TABLE documents (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug       text NOT NULL,
  deleted_at timestamptz
);

CREATE UNIQUE INDEX documents_live_slug_uidx
  ON documents (slug)
  WHERE deleted_at IS NULL;

slug 列そのものには UNIQUE を付けない。代わりに WHERE deleted_at IS NULL を満たす行だけをインデックスの対象にし、その範囲で slug の一意性を保証する。deleted_at に時刻が入った行はインデックスに載らないので、一意性の判定に関わらない。適用する。

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

まず、未削除の重複がきちんと止まることを確かめる。report-2024 を1件入れ、もう1件を未削除で入れようとする。

docker compose exec db psql -U app -d app -c "
INSERT INTO documents (slug) VALUES ('report-2024');"
docker compose exec db psql -U app -d app -c "
INSERT INTO documents (slug) VALUES ('report-2024');"

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "documents_live_slug_uidx"
DETAIL:  Key (slug)=(report-2024) already exists.

2件目は止まる。ここは素の UNIQUE と同じ動きだ。違いが出るのはこの次で、1件目を論理削除してから、同じ slug をもう一度入れる。

docker compose exec db psql -U app -d app -c "
UPDATE documents SET deleted_at = now() WHERE slug = 'report-2024' AND deleted_at IS NULL;
INSERT INTO documents (slug) VALUES ('report-2024');"

UPDATE 1
INSERT 0 1

今度は通る。削除済みになった1件目はインデックスの対象から外れ、未削除の report-2024 は新しい行だけになったからだ。状態を見る。

docker compose exec db psql -U app -d app -c "
SELECT id, slug, deleted_at IS NULL AS is_live FROM documents WHERE slug = 'report-2024' ORDER BY id;"

 id |    slug     | is_live
----+-------------+---------
  1 | report-2024 | f
  3 | report-2024 | t
(2 rows)

削除済み（is_live = f）と未削除（is_live = t）が同じ slug で共存している。未削除は常に1件、削除済みは何件あってもよい、という当初のルールがそのまま成り立つ。

id が 2 ではなく 3 に飛んでいるのは、途中で失敗した INSERT も GENERATED ALWAYS AS IDENTITY の採番を1つ消費するため。連番が飛ぶだけで、一意性の判定には関係しない。

インデックスの姿は \d で確認できる。

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

                                  Table "public.documents"
   Column   |           Type           | Collation | Nullable |           Default
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null | generated always as identity
 slug       | text                     |           | not null |
 deleted_at | timestamp with time zone |           |          |
Indexes:
    "documents_pkey" PRIMARY KEY, btree (id)
    "documents_live_slug_uidx" UNIQUE, btree (slug) WHERE deleted_at IS NULL

末尾に WHERE deleted_at IS NULL が付いている。これが「未削除の行だけを対象にする」という条件そのものだ。

6. 「特定の値だけ一意」に広げる

同じ仕組みは、論理削除に限らず「特定の条件を満たす行だけ一意」全般に使える。定番は「1ユーザーにつき、代表（プライマリ）は1件まで。それ以外は何件でもよい」だ。WHERE is_primary を条件にする。

docker compose exec db psql -U app -d app -c "
CREATE TABLE addresses (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL,
  label      text NOT NULL,
  is_primary boolean NOT NULL DEFAULT false
);
CREATE UNIQUE INDEX addresses_one_primary_uidx
  ON addresses (user_id)
  WHERE is_primary;"

条件が真偽値のときは WHERE is_primary とだけ書けば、is_primary = true の行だけがインデックス対象になる。ユーザー1に、プライマリ1件と非プライマリ2件を入れる。

docker compose exec db psql -U app -d app -c "
INSERT INTO addresses (user_id, label, is_primary) VALUES
  (1, 'home', true),
  (1, 'office', false),
  (1, 'warehouse', false);"

INSERT 0 3

非プライマリは何件でも入る。ここでユーザー1に2件目のプライマリを足そうとすると止まる。

docker compose exec db psql -U app -d app -c "
INSERT INTO addresses (user_id, label, is_primary) VALUES (1, 'billing', true);"

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

一方、別のユーザーは自分のプライマリを1件持てる。user_id 単位で一意なので、ユーザー2は独立している。

docker compose exec db psql -U app -d app -c "
INSERT INTO addresses (user_id, label, is_primary) VALUES (2, 'home', true);
SELECT user_id, label, is_primary FROM addresses ORDER BY user_id, id;"

 user_id |   label   | is_primary
---------+-----------+------------
       1 | home      | t
       1 | office    | f
       1 | warehouse | f
       2 | home      | t
(4 rows)

「特定の値（is_primary = true）だけ一意にし、それ以外は重複を許す」が、インデックス側の1行の条件で表せる。

7. 使うときの注意

`ON CONFLICT` には同じ条件が要る

5章の documents を続けて使う（未削除の report-2024 が1件残っている状態）。INSERT ... ON CONFLICT で重複時の挙動を書くとき、部分ユニークインデックスを狙うには推論に同じ WHERE を渡す。条件なしで書くと、PostgreSQL は対応する制約を見つけられない。次はエラーになる。

docker compose exec db psql -U app -d app -c "
INSERT INTO documents (slug) VALUES ('report-2024')
  ON CONFLICT (slug) DO NOTHING;"

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

インデックスと同じ WHERE deleted_at IS NULL を添えると、部分インデックスが推論され、狙いどおり動く。

docker compose exec db psql -U app -d app -c "
INSERT INTO documents (slug) VALUES ('report-2024')
  ON CONFLICT (slug) WHERE deleted_at IS NULL DO NOTHING;"

INSERT 0 0

INSERT 0 0 は、未削除の report-2024 が既にあり、競合したので何も挿入しなかったことを表す。

`NULLS NOT DISTINCT` という別解との違い

4章の複合 UNIQUE の落とし穴は、PostgreSQL 15 以降なら UNIQUE NULLS NOT DISTINCT (slug, deleted_at) でも塞げる。この宣言は NULL 同士を「同じ値」と見なすので、未削除の (report-2024, NULL) が2件あれば重複と判定される。既定の NULLS DISTINCT から変わるのはこの NULL の扱いだけで、deleted_at に時刻が入った非 NULL 同士の等値判定はどちらでも同じだ。ただし部分インデックスとは1点違う。この複合制約は deleted_at が同じ非 NULL 値の行も重複と判定するため、同じ slug を同一時刻に2件論理削除できない。部分インデックスは削除済みの行を対象から外すので、この制限がない。

用途が「未削除だけ一意」で、削除済みの行に一意制約をかけたくないなら、WHERE deleted_at IS NULL の部分インデックスが条件をそのまま表せる。is_primary を true / false の2値で持たせる設計では NULLS NOT DISTINCT が真偽条件に効かず、WHERE is_primary の部分インデックスが条件を直接表す。列を true / NULL で持たせて UNIQUE (user_id, is_primary) とすれば部分インデックスなしでも同じ制限を表せるが、その場合は「NULL が非プライマリ」という約束をスキーマの外で覚えておくことになる。

8. まとめ

確認したことは次のとおり。

素の UNIQUE (slug) は行の状態を見ない。論理削除した値を新しい行で使い回せない
UNIQUE (slug, deleted_at) は、未削除行の deleted_at がすべて NULL で、既定では NULL 同士が重複と見なされない。止めたい未削除の重複がすり抜ける
CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL は、条件を満たす行だけを一意にする。未削除は1件、削除済みは何件でも、というルールがそのまま成り立つ
条件は真偽値でもよい。WHERE is_primary で「1ユーザー1プライマリ」のような一意性を表せる
ON CONFLICT で狙うときは、インデックスと同じ WHERE を推論に渡す

一意性を「表全体」だと固定して考えると、論理削除やプライマリ指定のような要件は素の UNIQUE に押し込めなくなる。「どの行の集合の中で一意にしたいか」を先に決め、その集合を WHERE で書くのが部分ユニークインデックスだ。制約の全体像は PostgreSQLの制約入門（PRIMARY KEY / UNIQUE / CHECK / FOREIGN KEY）、外部キーの削除時挙動は PostgreSQLの外部キーと削除ルールを整理する（CASCADE / RESTRICT / SET NULL）にまとめてある。

PostgreSQLで「特定の値だけ重複を許す」ユニーク制約（部分ユニークインデックス）

この記事の前提

目次

前提環境

1. 何をしたいのか

2. 最小デモ環境を作る

3. 素の `UNIQUE` は「消したら再利用」ができない

4. 複合 `UNIQUE (slug, deleted_at)` の落とし穴

5. 部分ユニークインデックスで「未削除だけ一意」にする

6. 「特定の値だけ一意」に広げる

7. 使うときの注意

`ON CONFLICT` には同じ条件が要る

`NULLS NOT DISTINCT` という別解との違い

8. まとめ

この記事の前提

目次

前提環境

1. 何をしたいのか

2. 最小デモ環境を作る

3. 素の UNIQUE は「消したら再利用」ができない

4. 複合 UNIQUE (slug, deleted_at) の落とし穴

5. 部分ユニークインデックスで「未削除だけ一意」にする

6. 「特定の値だけ一意」に広げる

7. 使うときの注意

ON CONFLICT には同じ条件が要る

NULLS NOT DISTINCT という別解との違い

8. まとめ

関連記事

GitHub Actionsで docker compose を使うPHP CI（PostgreSQL付き）

PHP + Docker で非同期ジョブを動かす最小構成（PostgreSQL キュー）

PostgreSQLのstuck job回収設計（timeout / reclaim / 再実行の安全な流れ）

3. 素の `UNIQUE` は「消したら再利用」ができない

4. 複合 `UNIQUE (slug, deleted_at)` の落とし穴

`ON CONFLICT` には同じ条件が要る

`NULLS NOT DISTINCT` という別解との違い