Postgres 条件唯一约束（无需选择）

Question

我有以下表格：

汽车：id，名称，状态

一旦我们有一个名称为“SOLD”的记录，我想对汽车的名称强制执行条件约束。

例如：

1, 'BMW', 'NEW'
2, 'BMW', 'NEW'
3, 'BMW', 'SOLD'
4, 'BMW', 'NEW'

前三条记录应该可以顺利插入。但是第四条记录不应该插入，因为我们现在有一个状态为SOLD的宝马。

这是我尝试过的：

CREATE UNIQUE INDEX cars_sold ON cars (name) WHERE (status = 'SOLD');

这并不像我预期的那样有效。这只能确保我们不能有2个已售出的BMW。

注意：我可以用“INSERT SELECT”实现这一点，但是这个表会变得非常大，所以我想要一个独特约束执行的性能。我也发现可以用“CONSTRAINT CHECK”来做到这一点，但我认为它在幕后基本上执行了一个“SELECT”。

有没有办法在不影响大表性能的情况下实现这一点？概念上看来我总是需要进行选择。

- user2158382

---

我认为你必须使用触发器来完成这个任务。数据结构相当奇特。为什么不直接进行更新呢？ - Gordon Linoff

@GordonLinoff 触发器是否需要在底层运行选择？我真的想避免选择的性能损失，否则我只会执行插入选择。 - user2158382

标准涉及当前记录中无法获得的信息。可能需要使用外部表格来跟踪状态。 - Ben

Answer 1

你无法使用约束来解决这个问题，因为约束总是检查数据库的当前状态，而不考虑修改应用的顺序。

我认为最好使用一个BEFORE INSERT触发器来解决这个问题，该触发器运行一个SELECT语句，以查找具有相同名称和状态SOLD的汽车。你在问题中创建的唯一部分索引应该使这样的SELECT非常快，无论表有多大。

- Laurenz Albe

---

我相信我的约束条件不需要知道修改的顺序，它只需要知道数据库的当前状态。回到我的例子，当应用编号4时，它并不关心1、2、3的应用顺序，它只关心#3是否有一个状态为“已售”的BMW。 - user2158382

另外，部分索引如何帮助我？是因为索引树现在要小得多，因为它只包含状态为“已售”的值吗？ - user2158382

如果您假设增加id反映了修改的时间，那么您是正确的（但如果存在高并发，请不要依赖此假设）。尽管如此，我仍然想不出一个能够反映这一点的约束条件。部分索引将非常快，因为它是唯一的-它可以快速命中匹配的叶子条目，而不必进行扫描。 - Laurenz Albe