连接表的Postgres唯一多列索引 [英] Postgres unique multi-column index for join table

问题描述

我在Postgres中有一个多对多联接表，我想索引到A)提高性能(显然)和B)强制唯一性.例如:

I have a many-to-many join table in Postgres that I would like to index to A) increase performance (obviously) and B) enforce uniqueness. For example:

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

是否可以在两列上使用一个索引来强制值唯一?我应该使用哪种类型的索引?

Is it possible to have a single index on two columns that enforces uniqueness in the values? What type of index should I use?

推荐答案

作为主键

如果该唯一键是主键，请执行以下操作:

As Primary Key

Do this if that unique is 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

Do this if that unique is non-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

If you have existing table, do this instead:

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

那个alter table显示以下消息:

That alter table display this message:

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


Query returned successfully with no result in 22 ms.

拖放

如果您想删除该约束(您可能希望通过3个字段的组合来使其唯一):

Drop

If you wanted to drop that constraint(you might want to make unique a combination of 3 fields):

ALTER TABLE tbl DROP CONSTRAINT tbl_unique;

索引&约束与空值

关于索引，来自Postgres文档:

Index & Constraint & Nulls

Regarding index, from Postgres doc:

当唯一时，PostgreSQL自动创建唯一索引 为表定义了约束或主键

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，例如:

If uniqueness depends on some rules, you shall use CREATE UNIQUE INDEX, for example:

为此:

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

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

该唯一项可以捕获这些重复项，数据库将拒绝该重复项:

That unique can catch these duplicates, this will be rejected by database:

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

但是UNIQUE CONSTRAINT无法捕获重复的null.空值用作未知数，它们用作通配符，这就是为什么允许在唯一约束中具有多个空值的原因.这将被数据库接受:

Yet that UNIQUE CONSTRAINT cannot catch duplicate nulls. Nulls serves as unknown, they serves as wildcard, that's why it's allowed to have multiple nulls in unique constraint. This will be accepted by database:

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，它允许延迟唯一性，因此可以接受上面的空值.

Think of UNIQUE CONSTRAINT that it allows deferred uniqueness, hence the acceptance of null values above.

如果除了唯一约束之外，每个a_id仅需要一个通配符(null b_id)，则需要添加UNIQUE INDEX. UNIQUE CONSTRAINT上不能有表达式. INDEX和UNIQUE INDEX可以.这将是您用于拒绝多个null的完整DDL；

If you want only one wildcard(null b_id) per a_id, aside from the unique constraint, you need to add a UNIQUE INDEX. UNIQUE CONSTRAINT can't have an expression on them. INDEX and UNIQUE INDEX can. This will be your complete DDL for rejecting multiple null;

这将是您完整的DDL:

This will be your complete 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;      

这将立即被您的数据库拒绝:

This will be rejected by your database now:

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

这篇关于连接表的Postgres唯一多列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！