Postgresql：有条件的唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16236365/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql: Conditionally unique constraint
提问by EoghanM
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);
The WHEREpart above is wishful thinking.
WHERE上面的部分是一厢情愿的想法。
Any way of doing this? Or should I go back to the relational drawing board?
有没有办法做到这一点？还是我应该回到关系绘图板？
回答by Craig Ringer
PostgreSQL doesn't define a partial (i.e. conditional) UNIQUEconstraint - however, you cancreate 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.
PostgreSQL 没有定义部分（即条件）UNIQUE约束 - 但是，您可以创建部分唯一索引。PostgreSQL 使用唯一索引来实现唯一约束，所以效果是一样的，你只是看不到 中列出的约束information_schema。
CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is NOT null);
See partial indexes.
请参阅部分索引。
回答by Peter Yeremenko
it has already been said that PG doesn't define a partial (ie conditional) UNIQUE constraint. Also documentation says that the preferred way to add a unique constraint to a table is ADD CONSTRAINTUnique Indexes
已经说过 PG 没有定义部分（即条件）UNIQUE 约束。还文档说向表添加唯一约束的首选方法是ADD CONSTRAINT唯一索引
The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.
向表添加唯一约束的首选方法是 ALTER TABLE ... ADD CONSTRAINT。使用索引来强制唯一约束可以被视为不应直接访问的实现细节。但是，应该注意没有必要在唯一的列上手动创建索引；这样做只会复制自动创建的索引。
There is a way to implement it using Exclusion Constraints, (thank @dukelion for this solution)
有一种方法可以使用Exclusion Constraints来实现它，（感谢 @dukelion 提供此解决方案）
In your case it will look like
在你的情况下，它看起来像
ALTER TABLE stop ADD CONSTRAINT myc EXCLUDE (col_a WITH =) WHERE (col_b IS null);
