获取引用(通过外键)表中特定行的所有行 [英] Get all the rows referencing (via foreign keys) a particular row in a table

问题描述

这看起来很简单，但是我找不到这个问题的答案.

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

我想要什么?一个主表，其中的行会在不再被引用(通过外键)时删除.该解决方案可能特定于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.

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