如何列出受级联删除影响的表 [英] How to list tables affected by cascading delete

问题描述

我正在尝试对15个以上的表执行级联删除，但是不确定所有必需的外键是否已正确配置。我想检查缺少的约束，而无需手动检查每个约束。

I'm trying to perform a cascading delete on 15+ tables but I'm not certain that all of the requisite foreign keys have been configured properly. I would like to check for missing constraints without manually reviewing each constraint.

有没有一种方法来获取将受到级联删除查询影响的表的列表？ / p>

Is there a way to obtain a list of tables that will be affected by a cascading delete query?

推荐答案

使用 pg_depend 。示例：

Use pg_depend. Example:

create table master (id int primary key);
create table detail_1 (id int, master_id int references master(id) on delete restrict);
create table detail_2 (id int, master_id int references master(id) on delete cascade);

select pg_describe_object(classid, objid, objsubid)
from pg_depend 
where refobjid = 'master'::regclass and deptype = 'n';

                  pg_describe_object
------------------------------------------------------
 constraint detail_1_master_id_fkey on table detail_1
 constraint detail_2_master_id_fkey on table detail_2
(2 rows)

deptype ='n'表示：

DEPENDENCY NORMAL-单独创建的
对象之间的普通关系。可以删除依赖对象而不会影响
引用的对象。只能通过指定CASCADE的
删除引用的对象，在这种情况下，也删除
依赖对象。

DEPENDENCY NORMAL - A normal relationship between separately-created objects. The dependent object can be dropped without affecting the referenced object. The referenced object can only be dropped by specifying CASCADE, in which case the dependent object is dropped, too.

使用 pg_get_constraintdef（） 获取约束定义：

Use pg_get_constraintdef() to get constraint definitions:

select 
    pg_describe_object(classid, objid, objsubid), 
    pg_get_constraintdef(objid)
from pg_depend 
where refobjid = 'master'::regclass and deptype = 'n';


                  pg_describe_object                  |                       pg_get_constraintdef
------------------------------------------------------+------------------------------------------------------------------
 constraint detail_1_master_id_fkey on table detail_1 | FOREIGN KEY (master_id) REFERENCES master(id) ON DELETE RESTRICT
 constraint detail_2_master_id_fkey on table detail_2 | FOREIGN KEY (master_id) REFERENCES master(id) ON DELETE CASCADE
(2 rows)

---

要查找级联依赖关系的完整链，我们应该使用递归并查看目录 pg_constraint 以获得

---

To find the full chain of cascading dependencies we should use recursion and look into the catalog pg_constraint to get id of a dependent table.

with recursive chain as (
    select classid, objid, objsubid, conrelid
    from pg_depend d
    join pg_constraint c on c.oid = objid
    where refobjid = 'the_table'::regclass and deptype = 'n'
union all
    select d.classid, d.objid, d.objsubid, c.conrelid
    from pg_depend d
    join pg_constraint c on c.oid = objid
    join chain on d.refobjid = chain.conrelid and d.deptype = 'n'
    )
select pg_describe_object(classid, objid, objsubid), pg_get_constraintdef(objid)
from chain;

这篇关于如何列出受级联删除影响的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！