Вопрос по sql, postgresql – Postgres уникальный многостолбцовый индекс для таблицы соединений

4

У меня есть таблица соединений «многие ко многим» в Postgres, которую я хотел бы проиндексировать на A) повышение производительности (очевидно) и B) обеспечение уникальности. Например:

<code>a_id | b_id
1    | 2     <- okay
1    | 3     <- okay
2    | 3     <- okay
1    | 3     <- not okay (same as row 2)
</code>

Можно ли иметь один индекс на два столбца, который обеспечивает уникальность значений? Какой тип индекса я должен использовать?

Ваш Ответ

2   ответа
5

PRIMARY KEY а такжеUNIQUE Синтаксис, как объяснил @Michael Buen, вы также можете создать явный индекс:

CREATE UNIQUE INDEX foo_a_b ON tbl(a_id, b_id);

Это обычный многостолбцовый индекс b-дерева (именно это и создает неявно синтаксис KEY).

11
As Primary Key

create table tbl(
   a_id int not null,
   b_id int not null,
   constraint tbl_pkey primary key(a_id,b_id)
);
Not Primary Key

Сделайте это, если этот уникальный ключ не является первичным:

create table tbl(

   -- other primary key here, e.g.:
   -- id serial primary key,

   a_id int not null,
   b_id int not null,
   constraint tbl_unique unique(a_id,b_id)
);
Existing Table

Если у вас есть существующая таблица, сделайте это вместо:

alter table tbl
      add constraint tbl_unique unique(a_id, b_id)

Эта таблица изменения отображает это сообщение:

NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "tbl_unique" for table "tbl"


Query returned successfully with no result in 22 ms.
Drop

Если вы хотите удалить это ограничение (вы можете сделать уникальным комбинацию из 3 полей):

ALTER TABLE tbl DROP CONSTRAINT tbl_unique;
Index & Constraint & Nulls

Относительно индекса, из Postgres doc:

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table

Источник:http://www.postgresql.org/docs/9.1/static/indexes-unique.html

Если уникальность зависит от некоторых правил, вы должны использоватьCREATE UNIQUE INDEX, например:

Учитывая это:

CREATE TABLE tbl
(
  a_id integer NOT NULL,
  b_id integer NULL  
);

alter table tbl
    add constraint tbl_unique unique(a_id, b_id);

Этот уникальный может поймать эти дубликаты, это будет отклонено базой данных:

insert into tbl values
(1,1),
(1,1);

Тем не менее, эта УНИКАЛЬНАЯ ОГРАНИЧЕНИЕ не может поймать повторяющиеся нули. Нули служат неизвестными, они служат подстановочными знаками, поэтому разрешено иметь несколько нулей в ограничении уникальности. Это будет принято базой данных:

insert into tbl values
(1,1),
(1,null), -- think of this null as wildcard, some real value can be assigned later.
(1,null); -- and so is this. that's why both of these nulls are allowed

Думать оUNIQUE CONSTRAINT что он допускает отложенную уникальность, следовательно, принятие нулевых значений выше.

Если вы хотите использовать только один подстановочный знак (нулевой b_id) для каждого a_id, кроме ограничения уникальности, вам нужно добавитьUNIQUE INDEX, УНИКАЛЬНОЕ ОГРАНИЧЕНИЕ не может иметь выражения на них.INDEX а такжеUNIQUE INDEX Можно. Это будет ваш полный DDL для отклонения нескольких нулей;

Это будет ваш полный DDL:

CREATE TABLE tbl
(
  a_id integer NOT NULL,
  b_id integer NULL  
);
alter table tbl
    add constraint tbl_unique unique(a_id, b_id);

create unique index tbl_unique_a_id on tbl(a_id) where b_id is null;      

Это будет отклонено вашей базой данных:

insert into tbl values
(1,1),
(1,null),
(1,null);

Это будет разрешено:

insert into tbl values
(1,1),
(1,null);

Относится кhttp://www.ienablemuch.com/2010/12/postgresql-said-sql-server2008-said-non.html

@Eelke Спасибо за добавление этой информации. Я ссылаюсь на документ здесь для справки:postgresql.org/docs/7.4/static/indexes-multicolumn.html  Мне просто интересно, почему они не подчеркивают, что многостолбцовый индекс все еще может использоваться одним a_id (но не одним b_id) в версии 9.1 docpostgresql.org/docs/9.1/static/indexes-multicolumn.html
@ScottMarlowe приятно знать, что 9.1 может использовать индекс, даже если вы используете правильные столбцы индекса :-), следовательно, подчеркивание. Postgresql - эффективная база данных, неудивительно, что ее используют Skype и Instagram
Обратите внимание, что хотя многоколонный индекс на (a_id, b_id) также можно использовать для поиска только по a_id, его нельзя использовать для поиска только по b_id. Таким образом, вы можете захотеть создать второй индекс для одного столбца в b_id.
@ Michael Buen Потому что в 9.1 планировщик запросов МОЖЕТ использовать индекс, который попадает только во второй или последующий столбец. Однако это не очень эффективно, и специалист по планированию будет склонен отдавать предпочтение последовательному сканированию.
@Eelke Отличное наблюдение! У меня просто был момент WTF, когда я ОБЪЯСНЯЛ некоторые запросы и менял порядок индексов, исправил это. bloudermilk

Похожие вопросы