Уникальное ограничение Postgres vs index

Как я могу понять документацию, следующие определения эквивалентны:

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

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

Ответ 1

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

Позвольте создать мастер тестовой таблицы с двумя столбцами, 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=#

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

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

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

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.

Ответ 2

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

Ответ 3

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

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

Полный текст

Так что скоростные показатели должны быть одинаковыми

Ответ 4

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

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

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

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

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

Ответ 5

Я прочитал это в документе:

ДОБАВИТЬ table_constraint [НЕ VALID]

Эта форма добавляет новое ограничение к таблице, используя тот же синтаксис, что и CREATE TABLE, плюс параметр NOT VALID, который в настоящее время разрешен только для ограничений внешнего ключа. Если ограничение помечено как NOT VALID, потенциально длительная начальная проверка, чтобы убедиться, что все строки в таблице удовлетворяют ограничению, пропускается. Ограничение по-прежнему будет применено к последующим вставкам или обновлениям (то есть они потерпят неудачу, если в ссылочной таблице нет соответствующей строки). Но база данных не будет предполагать, что ограничение выполняется для всех строк в таблице, пока оно не будет проверено с помощью параметра VALIDATE CONSTRAINT.

Поэтому я думаю, что это то, что вы называете "частичной уникальностью", добавляя ограничение.

А о том, как обеспечить уникальность:

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

Примечание. Предпочтительным способом добавления уникального ограничения в таблицу является ALTER TABLE… ADD CONSTRAINT. Использование индексов для обеспечения уникальных ограничений может рассматриваться как деталь реализации, к которой нельзя обращаться напрямую. Однако следует помнить, что нет необходимости вручную создавать индексы для уникальных столбцов; это просто дублирует автоматически созданный индекс.

Поэтому мы должны добавить ограничение, которое создает индекс, чтобы обеспечить уникальность.

Как я вижу эту проблему?

"Ограничение" направлено на то, чтобы грамматически гарантировать, что этот столбец должен быть уникальным, он устанавливает закон, правило; в то время как "индекс" является семантическим, о "как реализовать, как добиться уникальности, что означает уникальность, когда дело доходит до реализации". Таким образом, способ, которым Postgresql реализует это, очень логичен: сначала вы объявляете, что столбец должен быть уникальным, затем Postgresql добавляет реализацию добавления уникального индекса для вас.

Ответ 6

Существует разница в блокировке.
Добавление индекса не блокирует доступ для чтения к таблице.
Добавление ограничения устанавливает блокировку таблицы, поэтому все выборки блокируются.