Click here to Skip to main content
14,869,890 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 10-Jan-18 20:40pm
v4
Comments
Maciej Los 11-Jan-18 2:34am
   
Well... you have to decide what you want to achieve: once you say: "i want to delete, but later: "find values"...

1 solution

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[^]
   
Comments
syam1986 11-Jan-18 2:47am
   
My existing tables does not contain the 'ON DELETE CASCADE'.
Maciej Los 11-Jan-18 2:51am
   
So, you have to alter tables ;)
Maciej Los 11-Jan-18 4:37am
   
Yes, it's. I can not assist you, because i don't have an access to your HDD or/and don't see your data. It's your job to read referenced documentation and resolve your issue.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900