获取所有引用(通过外键)表中特定行的行

问题描述

这看起来很简单，但我一直无法找到这个问题的答案.

This seems so simple, but I haven't been able to find an answer to this question.

我想要什么?一个主表，其中的行在不再被引用(通过外键)时会自行删除.该解决方案可能特定于 PostgreSql，也可能不特定于 PostgreSql.

What do I want? A master table with rows that delete themselves whenever they are not referenced (via foreign keys) anymore. The solution may or may not be specific to PostgreSql.

怎么做?我解决这个问题的一种方法(实际上是迄今为止唯一的方法)涉及以下内容:对于引用此主表的每个表，在 UPDATE 或 DELETE 行，以检查 master 中的引用行，还有多少其他行仍然引用引用的行.如果它下降到零，那么我也会在 master 中删除该行.

How? One of my approaches to solving this problem (actually, the only approach so far) involves the following: For every table that references this master table, on UPDATE or DELETE of a row, to check for the referenced row in master, how many other other rows still refer to the referenced row. If it drops down to zero, then I delete that row in master as well.

(如果你有更好的想法，我很想知道！)

(If you have a better idea, I'd like to know!)

详细说明:我有一个被许多其他人引用的主表

In detail:I have one master table referenced by many others

CREATE TABLE master (
  id serial primary key,
  name text unique not null
);

所有其他表格通常具有相同的格式:

All the other tables have the same format generally:

CREATE TABLE other (
  ...
  master_id integer references master (id)
  ...
);

如果其中一个不是 NULL，它们指的是 master 中的一行.如果我去这个并尝试删除它，我会收到一条错误消息，因为它已经被引用:

If one of these are not NULL, they refer to a row in master. If I go to this and try to delete it, I will get an error message, because it is already referred to:

ERROR:  update or delete on table "master" violates foreign key constraint "other_master_id_fkey" on table "other"
DETAIL:  Key (id)=(1) is still referenced from table "other".
Time: 42.972 ms

请注意，即使我有许多引用 master 的表，也不会花费太长时间来解决这个问题.如何在不引发错误的情况下找出这些信息?

Note that it doesn't take too long to figure this out even if I have many tables referencing master. How do I find this information out without having to raise an error?

推荐答案

您可以执行以下操作之一:

You can do one of the following:

1) 将reference_count 字段添加到主表.每当添加具有此 master_id 的行时，在明细表上使用触发器会增加 reference count.当行被删除时减少计数.当 reference_count 达到 0 - 删除记录.

1) Add reference_count field to master table. Using triggers on detail tables increase the reference count whenever a row with this master_id is added. Decrease the count, when row gets deleted. When reference_count reaches 0 - delete the record.

2) 使用 pg_constraint 表(详情 此处) 获取引用表列表并创建动态 SQL 查询.

2) Use pg_constraint table (details here) to get the list of referencing tables and create a dynamic SQL query.

3) 在每个明细表上创建触发器，删除主表中的 master_id.使用 BEGIN ... EXCEPTION ... END 忽略错误消息.

3) Create triggers on every detail table, that deletes master_id in main table. Silence error messages with BEGIN ... EXCEPTION ... END.

这篇关于获取所有引用(通过外键)表中特定行的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！