给定一个包含以下表的postgreSQL查询:
+--------------------+
|Foo |
+--------------------+
| id | value1 | refe |
+--------------------+
+--------------------+
|Bar |
+--------------------+
| id | value2 | refe |
+--------------------+
+-------------+
|Refe |
+-------------+
| id | value3 |
+-------------+
其中
refe
列是id
的Refe
外键。现在Refe
存储与Foo
和Bar
相关的附加数据(以及潜在的其他表)。我创建了一个存储“active”的视图,换句话说:
refe
和refe
的Foo
字段的并集:CREATE OR REPLACE VIEW liverefe AS
(SELECT refe FROM Foo)
UNION (SELECT refe FROM Bar)
现在,当从
Bar
或Foo
中删除一行时,Bar
可能会死掉(没有其他refe
或Foo
行引用它)。在这种情况下,它应该被移除。我们能解释一下如何实现这样的“引用计数”触发器吗?
最佳答案
使用NOT EXISTS
反半连接。
要删除refe
中的所有死行:
DELETE FROM refe r
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE refe = r.id)
AND NOT EXISTS (SELECT 1 FROM bar WHERE refe = r.id);
(可能)从refe中删除一个引用表中
DELETE
/UPDATE
之后的特定行:DELETE FROM refe r
WHERE r.id = 12345 -- your id here
AND NOT EXISTS (SELECT 1 FROM foo WHERE refe = r.id)
AND NOT EXISTS (SELECT 1 FROM bar WHERE refe = r.id);
与此触发器的完整代码示例相关的答案:
Foreign key contraints in many-to-many relationships
如果您应该在更新
refe
列的habbit中,请添加如下触发器:CREATE TRIGGER foo_updelaft_kill_orphaned_refe
AFTER DELETE OR UPDATE OF refe
ON foo
FOR EACH ROW EXECUTE PROCEDURE f_trg_kill_orphaned_refe();
同样适用于表
bar
等。这样,只有当
UPDATE
实际指向refe
列时才会调用触发器。Details in the manual.