[解決済み】Postgresの一意制約とインデックス

質問

私の理解では ドキュメント は、以下の定義と同等です。

create table foo (
    id serial primary key,
    code integer,
    label text,
    constraint foo_uq unique (code, label));

create table foo (
    id serial primary key,
    code integer,
    label text);
create unique index foo_idx on foo using btree (code, label);    

ただし Postgres 9.4 のマニュアル にはこう書かれています。

テーブルに一意制約を追加する好ましい方法は ALTER TABLE ... ADD CONSTRAINT . 一意性制約を強制するためのインデックスの使用は は、実装の詳細であり、使用すべきではありません。 に直接アクセスすることができます。

(編集: この注記はPostgres 9.5でマニュアルから削除されました。)

それは単にスタイルの問題なのか？これらの変種のいずれかを選択した場合、実際どのような影響があるのでしょうか (例えば、パフォーマンスにおいて)？

どのように解決するのか？

この基本的な、しかし重要な問題に疑問を感じたので、例を挙げて学ぶことにしました。

テストテーブルを作成しましょう マスター を2つのカラムで構成しています。 con_id 一意制約と ind_id は一意なインデックスを持つ。

create table master (
    con_id integer unique,
    ind_id integer
);
create unique index master_unique_idx on master (ind_id);

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_unique_idx" UNIQUE, btree (ind_id)

テーブルの説明(psql の \d) で、一意制約と一意インデックスを区別することができます。

一意性

念のため、一意性を確認しておこう。

test=# insert into master values (0, 0);
INSERT 0 1
test=# insert into master values (0, 1);
ERROR:  duplicate key value violates unique constraint "master_con_id_key"
DETAIL:  Key (con_id)=(0) already exists.
test=# insert into master values (1, 0);
ERROR:  duplicate key value violates unique constraint "master_unique_idx"
DETAIL:  Key (ind_id)=(0) already exists.
test=#

期待通りに動作しています

外部キー

ここで 詳細 テーブルの2つのカラムを参照する2つの外部キーがあります。 マスター .

create table detail (
    con_id integer,
    ind_id integer,
    constraint detail_fk1 foreign key (con_id) references master(con_id),
    constraint detail_fk2 foreign key (ind_id) references master(ind_id)
);

    Table "public.detail"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Foreign-key constraints:
    "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

さて、エラーはありません。動作確認をしてみましょう。

test=# insert into detail values (0, 0);
INSERT 0 1
test=# insert into detail values (1, 0);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk1"
DETAIL:  Key (con_id)=(1) is not present in table "master".
test=# insert into detail values (0, 1);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk2"
DETAIL:  Key (ind_id)=(1) is not present in table "master".
test=#

どちらのカラムも外部キーで参照することができます。

インデックスを用いた制約

既存のユニークインデックスを使用して、テーブル制約を追加することができます。

alter table master add constraint master_ind_id_key unique using index master_unique_idx;

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_ind_id_key" UNIQUE CONSTRAINT, btree (ind_id)
Referenced by:
    TABLE "detail" CONSTRAINT "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    TABLE "detail" CONSTRAINT "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

これで、カラム制約の記述に違いはありません。

パーシャルインデックス

テーブル制約の宣言では、部分インデックスを作成することはできません。 これは直接 定義 の create table ... . ユニークインデックス宣言では WHERE clause で部分インデックスを作成します。 また インデックス作成 を(カラムだけでなく)式に追加し、他のいくつかのパラメータ(照合順序、ソート順序、NULLの配置)を定義します。

あなた できない 部分インデックスを使用してテーブル制約を追加します。

alter table master add column part_id integer;
create unique index master_partial_idx on master (part_id) where part_id is not null;

alter table master add constraint master_part_id_key unique using index master_partial_idx;
ERROR:  "master_partial_idx" is a partial index
LINE 1: alter table master add constraint master_part_id_key unique ...
                               ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.