- a_id | b_id
- 1 | 2 <- okay
- 1 | 3 <- okay
- 2 | 3 <- okay
- 1 | 3 <- not okay (same as row 2)
是否可以在两列上使用单个索引来强制值中的唯一性？我应该使用什么类型的索引？
解决方法
如果唯一是主键,请执行此操作：
- create table tbl(
- a_id int not null,b_id int not null,constraint tbl_pkey primary key(a_id,b_id)
- );
不是主键
如果该唯一是非主键,请执行此操作：
- create table tbl(
- -- other primary key here,e.g.:
- -- id serial primary key,a_id int not null,constraint tbl_unique unique(a_id,b_id)
- );
现有表格
如果您有现有表,请改为：
- alter table tbl
- add constraint tbl_unique unique(a_id,b_id)
alter table显示以下消息：
- NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "tbl_unique" for table "tbl"
- Query returned successfully with no result in 22 ms.
下降
如果您想删除该约束(您可能希望将3个字段组合成唯一)：
- ALTER TABLE tbl DROP CONSTRAINT tbl_unique;
指数&约束&空值
关于索引,来自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);
然而,UNIQUE CONSTRAINT无法捕获重复的空值. Nulls用作未知数,它们用作通配符,这就是为什么它允许在唯一约束中有多个空值.这将被数据库接受：
- insert into tbl values
- (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它允许延迟唯一性,因此接受上面的空值.
如果每个a_id只需要一个通配符(null b_id),除了唯一约束外,还需要添加UNIQUE INDEX. UNIQUE CONSTRAINT不能在它们上面有表达式. INDEX和UNIQUE INDEX可以.这将是您拒绝多个null的完整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,null);
这将是允许的：
- insert into tbl values
- (1,null);
与http://www.ienablemuch.com/2010/12/postgresql-said-sql-server2008-said-non.html有关
