網頁

▼

2023/6/21

PostgreSQL 欄位唯一限制 unique constraint與unique index差異

PostgreSQL的unique constraint（唯一限制）與unique index（唯一索引）差別如下。


基本上兩者作用相同，都是限制欄位值必須是唯一，而unique constraint其背後也是以unique index。換句話說unique constraint會自動設定一個unique index。

下面建立一個資料表mytable，
欄位uc_id加上unique constraint；
欄位ui_id加上unique index。

CREATE TABLE mytable (
    uc_id INTEGER, -- unique constraint column
    ui_id INTEGER, -- unique index column
    CONSTRAINT uc_id_unique_key UNIQUE (uc_id)
);
CREATE UNIQUE INDEX ui_id_unique_index ON mytable (ui_id);

使用psql \d mytable查看mytable設定如下。可以看到uc_id也被加了名稱為uc_id_unique_key的index。

postgres=# \d mytable
              Table "public.mytable"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 uc_id  | integer |           |          |
 ui_id  | integer |           |          |
Indexes:
    "uc_id_unique_key" UNIQUE CONSTRAINT, btree (uc_id)
    "ui_id_unique_index" UNIQUE, btree (ui_id)

新增第一筆資料。

postgres=# INSERT INTO mytable VALUES (0, 0);
INSERT 0 1

新增另一筆資料，欄位uc_id值出現違反唯一限制錯誤，為unique constraint的效果。

postgres=# INSERT INTO mytable VALUES (0, 1);
ERROR:  duplicate key value violates unique constraint "uc_id_unique_key"
DETAIL:  Key (uc_id)=(0) already exists.

新增另一筆資料，欄位ui_id值出現違反唯一限制錯誤，為unique index的效果。

postgres=# INSERT INTO mytable VALUES (1, 0);
ERROR:  duplicate key value violates unique constraint "ui_id_unique_index"
DETAIL:  Key (ui_id)=(0) already exists.

使用下面語法在ui_id加上unique constraint限制，使用原本的unique indexui_id_unique_index。

ALTER TABLE mytable ADD CONSTRAINT ui_id_unique_key UNIQUE USING INDEX ui_id_unique_index;

執行後可看原本ui_id_unique_index重命名為ui_id_unique_key。

postgres=# ALTER TABLE mytable ADD CONSTRAINT ui_id_unique_key UNIQUE USING INDEX ui_id_unique_index;
NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "ui_id_unique_index" to "ui_id_unique_key"
ALTER TABLE

重新查看mytable設定如下，可以看到原本的ui_id的unique index變為unique constraint。

postgres=# \d mytable
              Table "public.mytable"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 uc_id  | integer |           |          |
 ui_id  | integer |           |          |
Indexes:
    "uc_id_unique_key" UNIQUE CONSTRAINT, btree (uc_id)
    "ui_id_unique_key" UNIQUE CONSTRAINT, btree (ui_id)


沒有留言:

張貼留言