Oracle Oracle hierarchical query to disable foreign keys

Question

I have some code (see below), which uses a hierarchical query that traverses down a table to find all the related Foriegn keys. This seems to be working fine.

Can this be modified not to display the top level table, in this case the TABLE_NAME='PARENT' as I want to wrap this code in PL/SQL in order to create the desired syntax to enable/disable Foriegn keys for all the descendants of a table.

In my example, I have 3 tables. A grandchild table, which REFERENCES a child table and a child table, which REFERENCES the parent table, which is the master table.

The reason for this is to clean up some massive tables and I am trying to figure out the most effective and efficient way to go about this exercise.

We don't have CASCADE DELETE setup as that is too dangerous and some people learned that the hard way unfortunately.

Below is my test case and expected results, which I plan to execute in a procedure.

create table parent (
  id NUMBER(10),
  value      varchar2(30),
constraint parent_pk primary key (id)
);

CREATE TABLE child
( id NUMBER(10) not null,
value NUMBER(10) not null,
constraint child_pk primary key (id,value),
CONSTRAINT parent_child_fk
FOREIGN KEY (id)
REFERENCES parent(id));

CREATE TABLE grandchild
( id NUMBER(10) not null,
value NUMBER(10) not null,
constraint grandchild_pk primary key (id,value),
CONSTRAINT child_grandchild_fk
FOREIGN KEY (id,value)
REFERENCES child(id,value));

insert into parent values (1,'a');
insert into parent values (2,'b');
insert into parent values (3,'c');

insert into child  values (1,1);
insert into child  values (1,2);
insert into child  values (1,3);
insert into child  values (2,1);
insert into child  values (2,2);
insert into child  values (2,3);
insert into child  values (3,1);
insert into child  values (3,2);
insert into child  values (3,3);

insert into grandchild  values (1,1);
insert into grandchild  values (1,2);
insert into grandchild  values (1,3);
insert into grandchild  values (2,1);
insert into grandchild  values (2,2);
insert into grandchild  values (2,3);
insert into grandchild  values (3,1);
insert into grandchild  values (3,2);
insert into grandchild  values (3,3);

In the query I hard code the column name ID.

If possible, I would like to figure it out else I can live with the hard coded value.

select distinct table_name, constraint_name, column_name, r_table_name, position, constraint_type 
from (
    SELECT uc.table_name, 
    uc.constraint_name, 
    cols.column_name, 
    (select table_name from user_constraints where constraint_name = uc.r_constraint_name) 
        r_table_name,
    (select column_name from user_cons_columns where constraint_name = uc.r_constraint_name and position = cols.position) 
        r_column_name,
    cols.position,
    uc.constraint_type
    FROM user_constraints uc
    inner join user_cons_columns cols on uc.constraint_name = cols.constraint_name 
    where constraint_type != 'C'
) 
start with table_name = 'PARENT' and column_name = 'ID'  
connect by nocycle 
prior table_name = r_table_name 
and prior column_name = r_column_name;

TABLE_NAME	CONSTRAINT_NAME	COLUMN_NAME	R_TABLE_NAME	POSITION	CONSTRAINT_TYPE
PARENT	PARENT_PK	ID	-	1	P
GRANDCHILD	CHILD_GRANDCHILD_FK	ID	CHILD	1	R
CHILD	PARENT_CHILD_FK	ID	PARENT	1	R

Expected results for my test CASE is to generate this syntax:

alter table CHILD disable constraint PARENT_CHILD_FK;
alter table GRANDCHILD disable constraint CHILD_GRANDCHILD_FK;

Answer 1

You can use:

SELECT 'ALTER TABLE "' || u.owner || '"."' || u.table_name || '" '
       || 'DISABLE CONSTRAINT "' || u.constraint_name || '"' AS statement
FROM   user_constraints u
       INNER JOIN user_constraints r
       ON (   u.constraint_type = 'R'
          AND r.constraint_type IN ('P', 'U')
          AND u.r_owner = r.owner
          AND u.r_constraint_name = r.constraint_name)
START WITH r.table_name       = 'PARENT'
CONNECT BY PRIOR u.owner      = r.owner
AND        PRIOR u.table_name = r.table_name;

db<>fiddle here