Hi,
I have a requirement where I have to delete data from a parent and from its child tables as well. I have prepared a procedure which will extract all child tables (till lowest level) and disabled constraints, deleted records from lowest level tables till parent table. This works fine when all child tables have same no of primary key/ Foreign key columns. This approach will not support the below scenario. We don't want to use ON DELETE CASCADE.
I have a table PARENT_LEVEL1 (p_col1, p_col2, col3,col4) -- p_col1,p_col2 are PK columns
I have another table CHILD_LEVEL2 (p_id, p_col6, p_col7)-- p_id,p_col6 and p_col7 are primary keys and p_col6,p_col7 refers to p_col1 and p_col2 (FK)
I have another table CHILD_LEVEL2a(p_col6,p_id,p_col7)-- p_col6,p_col7 and p_id are pk columns and p_col6,p_col7 refers to p_col1 and p_col2 (FK)
I have another table CHILD_LEVEL3 (col8,col9,col10) -- col8,col9,col10 refers to p_id, p_col6, p_col7(FK).
Now, I have to delete data from PARENT_LEVEL1 table.
Step1: I am using below query to fetch all child tables for PARENT_LEVEL1 which is working fine.
with t
as
(
select table_name, constraint_name pkey_constraint, null fkey_constraint, null r_constraint_name
from user_constraints
where constraint_type = 'P'
union all
select a.table_name,
a.constraint_name pkey_constraint,
b.constraint_name fkey_constraint,
b.r_constraint_name
from user_constraints a, user_constraints b
where a.table_name = b.table_name
and a.constraint_type = 'P'
and b.constraint_type = 'R'
),--select * from temp_constraints
t1 AS (select t.*, level lvl
from t
start with fkey_constraint is null and table_name ='PARENT_LEVEL1'
connect by pkey_constraint <> r_constraint_name AND prior pkey_constraint = r_constraint_name
)
select * from t1 ORDER BY lvl desc
Step2: I am preparing dynamic SQL to delete data (based on a condition from all the child tables). To get the column names from child tables (as these columns need not to have the same name as in parent table), i am using below query to get column names. Now, with this, I will get additional column p_id at any position and I can't predict the position of this columns and value as well. Hence, I can't build any delete query to delete data from CHILDLEVEL3 based on some records from PARENT_LEVEL1 table.
SELECT * FROM all_cons_columns a , all_constraints b where a.table_name = b.table_name
and a.constraint_name = b.constraint_name and a.owner ='SCPOMGR' and a.table_name = 'CHILD_LEVEL3'
and b.constraint_type = 'R' ORDER BY position
Is there any way we can point these child records to ultimate parent table (along with the right mapping of columns, in this case, we should ignore p_id column).
Any inputs would be really helpful.
Thanks!!!