如何删除包括父表在内的所有子表中的数据。 [英] How to delete data in all child tables including parent table.

问题描述

我有几张表与下面的外键有约束关系：



1.parent_table id（主键）

2。 parent_child c_id（主键）和id（来自parent_table的引用）

3.child ch_id（主键）和c_id（来自parent_child表的引用）



现在我需要删除parent_table中的记录，包括具有相同记录的子表。但是我在接受挑战是parent_table引用parent_child表，但parent_child表引用子表与另一列。



如何从子表中识别确切值以删除该特定值记录。



提前致谢。



我的尝试：

I have few tables having constraint relationship with foreign keys like below:

1.parent_table id(primary key)
2.parent_child c_id(primary key) and id(reference from parent_table)
3.child ch_id(primary key) and c_id(reference from parent_child table)

Now i need to delete a record from parent_table including with child tables which are having same record. But here i am getting challenge is parent_table references parent_child table but parent_child table references child table with another column.

How can i identify exact value from child tables to delete that particular record.

Thanks in advance.

What I have tried:

create table parent(id number primary key,name varchar2(30));

create table parent_child(c_id number primary key,id number,name varchar2(30));

ALTER TABLE parent_child ADD
    CONSTRAINT parent_child_fk FOREIGN KEY (id)
       REFERENCES parent(id);
       
create table child(ch_id number primary key,c_id number,name varchar2(30));

ALTER TABLE child ADD
    CONSTRAINT child_fk FOREIGN KEY (c_id)
       REFERENCES parent_child(c_id);


I have tried below query to find the tables:

<pre>
SELECT DISTINCT LEVEL,  PT AS "TNAME",COLUMN_NAME
            FROM (SELECT A.OWNER W1,
                         A.table_name PT,
                         A.constraint_name C1,
                         A.r_constraint_name R1,
                         B.OWNER W2,
                         B.table_name CT,
                         B.constraint_name C2,
                         B.r_constraint_name R2,
                         C.COLUMN_NAME
                    FROM ALL_CONSTRAINTS A, all_constraints B,ALL_CONS_COLUMNS C
                   WHERE     (A.constraint_name = b.r_constraint_name(+))
                   AND A.CONSTRAINT_NAME=C.CONSTRAINT_NAME
                         AND A.OWNER = UPPER ('USER')
                         AND B.OWNER(+) = UPPER ('USER') 
                         AND A.r_constraint_name IS NULL
                         AND A.constraint_type IN ('P', 'R')) V1
      START WITH PT = UPPER ('PARENT') 
      CONNECT BY PRIOR CT = PT AND LEVEL <= 10 ORDER BY LEVEL

::此查询仅返回列名。我想将此查询放在隐式游标中，并使用父表列值查找值。

:: This query returning only column names.. I would like to put this query in the implicit cursor and find the values with parent table column value.

推荐答案

请阅读我对这个问题的评论...



如果你想删除，我强烈建议你阅读：Oracle / PLSQL：带有级联删除的外键 [ ^ ]

Please, read my comment to the question...

If you want to delete, i'd strongly recommend to read this: Oracle / PLSQL: Foreign Keys with Cascade Delete[^]

这篇关于如何删除包括父表在内的所有子表中的数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！