PostgreSQL:条件唯一约束 [英] Postgresql: Conditionally unique constraint

问题描述

我想添加一个约束，该约束仅在表的一部分中对列强制实施唯一性.

I'd like to add a constraint which enforces uniqueness on a column only in a portion of a table.

ALTER TABLE stop ADD CONSTRAINT myc UNIQUE (col_a) WHERE (col_b is null);

上面的WHERE部分是一厢情愿.

The WHERE part above is wishful thinking.

有什么办法吗?还是我应该回到关系绘图板上?

Any way of doing this? Or should I go back to the relational drawing board?

推荐答案

PostgreSQL未定义部分(即有条件的)UNIQUE约束-但是，您可以创建部分唯一的索引. PostgreSQL使用唯一索引来实现唯一约束，因此效果是一样的，您不会在information_schema中看到约束.

PostgreSQL doesn't define a partial (i.e. conditional) UNIQUE constraint - however, you can create a partial unique index. PostgreSQL uses unique indexes to implement unique constraints, so the effect is the same, you just won't see the constraint listed in information_schema.

CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is NOT null);

请参见部分索引.

这篇关于PostgreSQL:条件唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！