Oracle-删除父级的所有子级记录 [英] Oracle - delete all child records for a parent

问题描述

我很难找到一种简单的方法来删除父ID的所有子记录.子表也可能有自己的子表，因此我们需要删除层次结构中的所有记录.最简单的方法是什么?我可以手动转到每个子表并找到它的子表，然后创建一个脚本，但是表太多了，想知道一种更简单的方法.任何帮助表示赞赏！

I am having a hard time finding an easy way to delete all child records of a parent id. The child tables may have their own child tables as well so we need to delete all the records in the hierarchy. What method is the easiest? I could manually go to each child table and find it's child tables and then create a script, but there are way too many tables and want to know an easier approach. Any help appreciated!

推荐答案

这几乎是主键，外键以及像ON DELETE CASCADE这样的子句的用途.如果还不算太晚，可以在删除之前尝试添加PK和FK约束.一切都会变得简单.

This is pretty much what primary keys and foreign keys and clauses like ON DELETE CASCADE are for. If it's not too late, you can try adding PK and FK constraints before you do any deletions; then everything will be easy.

添加:基于进一步的讨论.下面的查询可用于查找父表的所有后代表.该查询可能会在许多方面得到改进，但这可能是一个不错的起点.

ADDED: Based on further discussion. The query below can be used to find all descendant tables of a parent table. The query may probably be improved in many ways, but it may be an OK starting point.

with f as (
        select constraint_name, table_name, r_constraint_name
        from   user_constraints
        where  constraint_type = 'R'
     ),
     p as (
        select constraint_name, table_name
        from   user_constraints
        where  constraint_type = 'P'
     ),
     j (child_table, f_key, parent_table, p_key) as (
        select f.table_name, f.constraint_name, p.table_name, f.r_constraint_name
        from   p join f on p.constraint_name = f.r_constraint_name
        union all
        select 'EMPLOYEES', (select constraint_name from p 
                                where table_name = 'EMPLOYEES'), null, null from dual
     )
select level as lvl, j.*
from j
start with parent_table is null
connect by nocycle parent_table = prior child_table
order by lvl, parent_table, child_table;

在这种情况下，父"表是EMPLOYEES，并且该名称在同一行上出现两次.如果需要，可以将其设置为绑定变量.我使用了EMPLOYEES(注意:它必须大写，因为这是将字符串值存储在系统表中的方式)，因为我是在标准HR模式上运行它的；输出:

The "parent" table in this case is EMPLOYEES and the name appears twice, on the same line. That can be made into a bind variable if needed. I used EMPLOYEES (note: it must be in all-caps because that's how string values are stored in system tables) because I ran this on the standard HR schema; output:

  LVL CHILD_TABLE       F_KEY                PARENT_TABLE      P_KEY
----- ----------------- -------------------- ----------------- -----------------
    1 EMPLOYEES         EMP_EMP_ID_PK
    2 DEPARTMENTS       DEPT_MGR_FK          EMPLOYEES         EMP_EMP_ID_PK
    2 JOB_HISTORY       JHIST_EMP_FK         EMPLOYEES         EMP_EMP_ID_PK
    3 JOB_HISTORY       JHIST_DEPT_FK        DEPARTMENTS       DEPT_ID_PK

这篇关于Oracle-删除父级的所有子级记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！