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

问题描述

这似乎很简单，但是我找不到这个问题的答案。

我想要什么？一个主表，其中的行会在不再被引用（通过外键）时删除它们。

如何？我的一个解决这个问题的方法（实际上，唯一的办法far）包含以下内容：对于引用此主表的每个表，在 UPDATE 或 DELETE 检查master中引用的行，有多少其他行仍引用引用的行。如果它下降到零，那么我删除master中的那一行。

（如果你有一个更好的主意，我想知道！）

详细：
我有一个主表由其他人引用

  CREATE TABLE master（
 id serial primary key，
 name text unique not null 
）; 
  

所有其他表格格式通常相同：

  CREATE TABLE other（
 ... 
 master_id整数引用主（id）
 ... 
）; 
  

如果其中一个不是 NULL 它们引用 master 中的一行。如果我去这个尝试删除它，我会得到一个错误信息，因为它已经被引用：

  ERROR ：在表master上更新或删除违反表other上的外键约束other_master_id_fkey
详细信息：键（id）=（1）仍从表其他引用。 
时间：42.972 ms 
  

请注意，这不需要太长时间即使我有很多表引用 master 。

解决方案

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

1）将 reference_count 字段添加到主表。当添加了具有此 master_id 的行时，对详细信息表使用触发器会增加引用计数当行被删除时，减少计数。 reference_count 达到0 - 删除记录。

2）使用 pg_constraint 表格（详情请参阅这里）

3）在每个详细信息表上创建触发器，删除 master_id 在主表中。带有 BEGIN ... EXCEPTION ... END 的静音错误消息。

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

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.

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)
  ...
);

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

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) 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) Use pg_constraint table (details here) to get the list of referencing tables and create a dynamic SQL query.

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

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