基于 SELECT 条件的 postgres 约束

Question

ᕼello！我想我有一个有点棘手的帖子情况：

parent有child任。 child仁有一个age，以及一个标志，表明他们是appreciated。

规则：父母不能欣赏两个同龄的孩子！

我的问题是：如何执行这个规则？

当前架构：

CREATE TABLE parent (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);
CREATE TABLE child (
  id SERIAL PRIMARY KEY,
  parent INTEGER REFERENCES parent(id) NOT NULL,
  name VARCHAR(50) NOT NULL,
  age INTEGER NOT NULL,
  appreciated BOOLEAN NOT NULL
);

输入一些值：

INSERT INTO parent(name) VALUES
  ('bob'),    -- assume bob's id = 0
  ('mary');   -- assume mary's id = 1
INSERT INTO child(parent, name, age, appreciated) VALUES
  (0, 'child1', 10, FALSE), -- Bob has children 1, 2, 3
  (0, 'child2', 10, FALSE),
  (0, 'child3', 15, FALSE),
  (1, 'child4', 20, FALSE), -- Mary has children 4, 5, 6
  (1, 'child5', 20, FALSE),
  (1, 'child6', 10, FALSE);

到目前为止一切都很好。没有孩子被欣赏，这总是有效的。

玛丽被允许欣赏孩子6：

UPDATE child SET appreciated=TRUE WHERE name='child6';

Bob被允許欣賞孩子2.孩子2與孩子6同齡(已經被欣賞(，但孩子6不是Bob的孩子。

UPDATE child SET appreciated=TRUE WHERE name='child2';

鲍勃现在无法欣赏孩子1。这个孩子1和孩子2同龄，孩子2已经很感激了。

UPDATE child SET appreciated=TRUE WHERE name='child2'; -- This needs to FAIL!

如何强制实施此类约束？我对各种解决方案持开放态度，但修改常规架构不是一种选择。

提前感谢！

Answer 1

UNIQUE部分索引怎么样，如下所示：

CREATE UNIQUE INDEX ON child(parent,age) WHERE appreciated;

所以每一对parent，age都必须是独一无二的，但前提是考虑到欣赏的孩子。

Answer 2

您可能希望使用在插入/更新之前激活的触发器，如果不满足您创建的约束，该触发器将失败。我想它应该是这样的

create trigger <trigger_name>
before insert or update on <table_name>
for each row
declare
dummy number;
begin
select count(*)
into dummy
from <table_name>
where (appreciated=TRUE and :new.child = child and :new.age = age);
if dummy > 0 then
  raise_application_error(-20001,'Too many appreciated children');
end if;
end;

一些文档

Answer 3

我想做的最简单的事情是添加一个标志 感恩(？( == 假到父模型和当孩子.赞赏 == 真 { parent.grateful == true }

在作用于child.appreciated的函数中检查parent.grateful 的值。如果父母感恩 == 真 返回"对不起，这位家长已经表达了他们的感激之情。大声笑，这是一个有趣的概念。祝你好运。:)