Disable all foreign key restrictions associated with a particular table

Question

I have a MY_TABLE table that has the primary key 'CODE', this primary key has several foreign key constraints that reference it, and I need to temporarily disable them.

We are trying to expand this answer here: Fix ORA-02273: this unique / primary key refers to some foreign keys , except for not constraint_name

I am trying to select all foreign_key constraints that reference "CODE" from "MY_TABLE" and disable them (eventually enable, I assume you just disable it for inclusion in the syntax)

Answer 1

, :

create table MY_TABLE ( CODE number primary key);
create table anotherTable ( code_ref number);
alter table  anotherTable add constraint ck1 foreign key ( code_ref) references my_table ( code);
create table yetAnotherTable ( code_ref number);
alter table  yetAnotherTable add constraint ck2 foreign key ( code_ref) references my_table ( code);

- , , , / :

begin
    for s in (
                SELECT 'alter table ' || c2.table_name || ' modify constraint ' || c2.constraint_name || ' disable' as statement
                FROM all_constraints c
                       INNER JOIN all_constraints c2
                         ON ( c.constraint_name = c2.r_constraint_name AND c.owner = c2.owner)
                       INNER JOIN all_cons_columns col
                         ON ( c.constraint_name = col.constraint_name AND c.owner = col.owner) 
                WHERE c2.constraint_type = 'R'
                  AND c.table_name = 'MY_TABLE'
                  AND c.owner = 'ALEK'
                  AND col.column_name = 'CODE'
             )
    loop
        dbms_output.put_line(s.statement);
        execute immediate s.statement;
    end loop;
end;

( ):

alter table YETANOTHERTABLE modify constraint CK2 disable
alter table ANOTHERTABLE modify constraint CK1 disable

Answer 2

, :

SELECT  'alter table ' || table_name || ' disable constraint ' ||  constraint_name || ';' from (
  select distinct a.table_name, a.constraint_name
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                    AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                       AND c.r_constraint_name = c_pk.constraint_name
  WHERE c.constraint_type = 'R'
  AND c_pk.table_name = 'MY_TABLE');

Answer 3

PL/SQL alter table. , , cascade, ( ).

:

--drop table t3;
--drop table t2;
--drop table t1;
create table t1(c1 number primary key);
create table t2(c1 number references t1(c1));
create table t3(c1 number references t1(c1));

select table_name
     , constraint_type
     , status
  from user_constraints
  where table_name in ('T1','T2', 'T3')

TABLE C STATUS    
----- - ----------
T2    R ENABLED   
T1    P ENABLED   
T3    R ENABLED   

3 rows selected.

:

alter table t1 disable primary key cascade;
alter table t1 enable  primary key;

:

select table_name
     , constraint_type
     , status
  from user_constraints
  where table_name in ('T1','T2', 'T3')

TABLE C STATUS    
----- - ----------
T2    R DISABLED  
T1    P ENABLED   
T3    R DISABLED  

3 rows selected.

Note . It is not possible to re-enable all foreign key constraints in cascading mode. This must be done manually.