sql - 使用过滤的行比较排除约束

问题描述

该示例基于https://www.postgresql.org/docs/current/btree-gist.html

假设我有一个架构：

CREATE TABLE zoo (
  cage   INTEGER,
  animal TEXT,
  is_agressive BOOLEAN,
  constraint no_different_animals_in_same_cage EXCLUDE USING gist (cage WITH =, animal WITH <>)
);

no_different_animals_in_same_cage防止两只不同的动物在同一个笼子里。我想要的是只有当其中一只动物时才具有这种约束is_aggressive IS TRUE。cage因此，只要没有将is_aggressive标志设置为，就可以在中存在带有斑马的鹿TRUE。

我该怎么做呢？

标签： sqlpostgresqlconstraints

如果您只是想防止攻击性和非攻击性动物生活在同一个笼子里，您可能想添加约束并将其强制is_aggressive转换EXCLUDE为支持的类型btree_gist，例如int4（1 = true，0 = false）：

 CREATE TABLE zoo (
  cage   INTEGER,
  animal TEXT,
  is_aggressive BOOLEAN,
  CONSTRAINT no_different_animals_in_same_cage 
    EXCLUDE USING gist (cage WITH =, animal WITH <>, int4(is_aggressive) WITH <>)
);

这将起作用：

INSERT INTO zoo VALUES(1,'zebra',false);
INSERT INTO zoo VALUES(1,'zebra',false);
INSERT INTO zoo VALUES(1,'deer',false);

INSERT INTO zoo VALUES(2,'lion',true);
INSERT INTO zoo VALUES(2,'lion',true);

这将失败：

INSERT INTO zoo VALUES(1,'lion',true);

ERROR:  conflicting key value violates exclusion constraint "no_different_animals_in_same_cage"
DETAIL:  Key (cage, animal, int4(is_aggressive))=(1, lion, 1) conflicts with existing key (cage, animal, int4(is_aggressive))=(1, zebra, 0).
SQL state: 23P01

请注意，仍然可以将两只具有攻击性的动物放入同一个笼子！为了防止它，您可以UNIQUE INDEX在表中添加一个，以便在EXCLUDE约束之上检查传入的记录以查看是否有攻击性动物已经生活在特定的笼子中：

CREATE UNIQUE INDEX aggressive_animals_live_alone
ON zoo (cage,is_aggressive) WHERE (is_aggressive);

现在这也将失败：

INSERT INTO zoo VALUES(2,'lion',true);
INSERT INTO zoo VALUES(2,'killer bunny',true);

ERROR:  duplicate key value violates unique constraint "aggressive_animals_live_alone"
DETAIL:  Key (cage, is_aggressive)=(2, t) already exists.

如果您想执行更复杂的检查，我建议您查看触发器。

演示：db<>fiddle

正如@Arkaduisz Noster 在评论中指出的那样，如果允许至少 1 只攻击性动物与非攻击性动物共享一个笼子，则可以在以下内容中添加一个WHERE子句CONSTRAINT：

CREATE TABLE zoo (
  cage   INTEGER,
  animal TEXT,
  is_aggressive BOOLEAN,
  CONSTRAINT no_different_animals_in_same_cage 
    EXCLUDE USING gist (cage WITH =, animal WITH <>) WHERE (is_aggressive)
);

演示：db<>fiddle

sql - 使用过滤的行比较排除约束

问题描述

解决方案

推荐阅读