PostgreSQLにおけるUNIQUE制約とUNIQUE INDEXの違い？

PostgreSQL

正直なところ勘違いをしていました。UNIQUEの制限をするためにUNIQUE INDEXをしばしば作成していましたので、この違いを調べました。

PostgreSQL 9.3.9においては、

  • UNIQUE 制約を作ると UNIQUE INDEX も作られるが、
  • UNIQUE INDEX を作っても UNIQUE 制約 は作られないようです。

そのためinformation_schema.table_constraintsテーブルに差が現われます。ただし、エラーメッセージは同じようです。

UNIQUE 制約( CREATE TABLE 時)

  • 作成
CREATE TABLE example1 (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    date DATE DEFAULT CURRENT_DATE NOT NULL,
    UNIQUE(user_id, date)
);

制約確認

SELECT constraint_name, table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'public'
AND table_name = 'example1'
  • 結果
constraint_name table_name constraint_type
example1_pkey example1 PRIMARY KEY
example1_user_id_date_key example1 UNIQUE

※NOT NULL制約は省略

INDEX確認

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'example1';
  • 結果
tablename indexname indexdef
example1 example1_pkey CREATE UNIQUE INDEX example1_pkey ON example1 USING btree (id)
example1 example1_user_id_date_key CREATE UNIQUE INDEX example1_user_id_date_key ON example1 USING btree (user_id, date)

エラー確認

INSERT INTO example1(user_id, date)
VALUES(1, CURRENT_DATE), (1, CURRENT_DATE);
  • エラーメッセージ
ERROR:  重複キーが一意性制約"example1_user_id_date_key"に違反しています

UNIQUE 制約( CREATE TABLE 後 ALTER TABLE で追加)

  • 作成
CREATE TABLE example2 (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    date DATE DEFAULT CURRENT_DATE NOT NULL
);

ALTER TABLE example2 ADD UNIQUE(user_id, date);

制約確認

SELECT constraint_name, table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'public'
AND table_name = 'example2'
  • 結果
constraint_name table_name constraint_type
example2_pkey example2 PRIMARY KEY
example2_user_id_date_key example2 UNIQUE

※NOT NULL制約は省略

INDEX確認

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'example2';
  • 結果
tablename indexname indexdef
example2 example2_pkey CREATE UNIQUE INDEX example2_pkey ON example2 USING btree (id)
example2 example2_user_id_date_key CREATE UNIQUE INDEX example2_user_id_date_key ON example2 USING btree (user_id, date)

エラー確認

INSERT INTO example2(user_id, date)
VALUES(1, CURRENT_DATE), (1, CURRENT_DATE);
  • エラーメッセージ
ERROR:  重複キーが一意性制約"example2_user_id_date_key"に違反しています

UNIQUE INDEX

  • 作成
CREATE TABLE example3 (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    date DATE DEFAULT CURRENT_DATE NOT NULL
);

CREATE UNIQUE INDEX example3_user_id_date_key
ON example3(user_id, date);

制約確認

SELECT constraint_name, table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'public'
AND table_name = 'example3'
  • 結果
constraint_name table_name constraint_type
example3_pkey example3 PRIMARY KEY

※NOT NULL制約は省略

INDEX確認

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'example3';
  • 結果
tablename indexname indexdef
example3 example3_pkey CREATE UNIQUE INDEX example3_pkey ON example3 USING btree (id)
example3 example3_user_id_date_key CREATE UNIQUE INDEX example3_user_id_date_key ON example3 USING btree (user_id, date)

エラー確認

INSERT INTO example3(user_id, date)
VALUES(1, CURRENT_DATE), (1, CURRENT_DATE);
  • エラーメッセージ
ERROR:  重複キーが一意性制約"example3_user_id_date_key"に違反しています