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 doesn't define a partial (i.e. conditional) UNIQUE constraint - however, you can create a partial unique index.

PostgreSQL 使用唯一索引来实现唯一约束，所以效果是一样的，但有一个重要的警告:你不能像你那样对唯一索引执行 upserts (ON CONFLICT DO UPDATE)针对唯一约束.

PostgreSQL uses unique indexes to implement unique constraints, so the effect is the same, with an important caveat: you can't perform upserts (ON CONFLICT DO UPDATE) against a unique index like you would against a unique constraint.

此外，您不会看到 information_schema 中列出的约束.

Also, you 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屋！