Уникальное ограничение 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);

однако вы можете прочитать в примечании:предпочтительный способ, чтобы добавить ограничение уникальности для таблицы Alter таблицы ... ДОБАВИТЬ ОГРАНИЧЕНИЕ. Использование индексов для применения уникальных ограничений можно рассматривать как деталь реализации, к которой не следует обращаться напрямую.

Это только вопрос хорошего стиля? Каковы практические последствия выбора один из этих вариантов (например, в производительности)?

4 107
sql postgresql unique

4 ответа:

у меня были некоторые сомнения по этому основному, но важному вопросу, поэтому я решил учиться на примере.

давайте создадим тестовую таблицу мастер С двумя колонками, 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)

в описании таблицы (\d в psql) вы можете определить уникальное ограничение из уникального индекса.

уникальность

давайте проверим уникальность, просто в случай.

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=#

он работает, как и ожидалось!

внешние ключи

теперь определим деталь таблица с двумя внешними ключами, ссылающимися на наши два столбца в мастер.

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 для создания частичного индекса. Вы также можете создать индекс по выражению (не только по столбцу) и определить некоторые другие параметры (параметры сортировки, порядок сортировки, размещение нулей).

вы не можете добавить ограничение таблицы с помощью частичного индекса.

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.
93

еще одно преимущество использования UNIQUE INDEX и UNIQUE CONSTRAINT что вы можете легко DROP/CREATE индекс CONCURRENTLY, тогда как с ограничением, вы не можете.

24

уникальность является ограничением. Это происходит, чтобы быть реализованы с помощью создания уникального индекса, так как индекс быстро может искать все существующие значения для того, чтобы определить, если данное значение уже существует.

концептуально индекс является деталью реализации и уникальность должна быть связано только с ограничениями.

полный текст

поэтому быстродействие должно быть одинаковым

6

еще одна вещь, с которой я столкнулся, заключается в том, что вы можете использовать выражения sql в уникальных индексах, но не в ограничениях.

Итак, это не работает:

CREATE TABLE users (
    name text,
    UNIQUE (lower(name))
);

но следующие работы.

CREATE TABLE users (
    name text
);
CREATE UNIQUE INDEX uq_name on users (lower(name));
1