Disable all foreign key constraints associated to specific table

Question

I have table 'MY_TABLE' which has a primary key 'CODE' , this primary key has several foreign key constraints referencing it and I need to disable them all temporarily.

Trying to expand on this answer here : Fix ORA-02273: this unique/primary key is referenced by some foreign keys, except not by constraint_name

I'm trying to select all foreign_key constraints that reference 'CODE' of 'MY_TABLE' and disable them (eventually enable, I'm assuming you just switch disable for enable in the syntax)

`I need to disable them all temporarily` ... why do you need to do this? It sounds that perhaps you have a design problem and maybe you should sort that out first. — Tim Biegeleisen, Dec 21 '16 at 14:41
@TimBiegeleisen Trying to fix a design problem, just found out that the index for our PK was not unique,from what I searched, to change it we need to disable all constraints on it — Mojimi, Dec 21 '16 at 14:43
Thanks for clarifying, in this case it sounds like it might be necessary. — Tim Biegeleisen, Dec 21 '16 at 14:44
[Possible duplicate?](http://stackoverflow.com/q/128623/266304) You can change the PL/SQL code that suggests to restrict by constraint type and/or `r_constraint_name` (using the PK name) to make it more specific. — Alex Poole, Dec 21 '16 at 15:01

score 8 · Accepted Answer · answered Dec 21 '16 at 15:08

Say you have tables like these:

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);

You can use something like the following to loop through all the constraints referring to a given column of a table and disable/enable them with:

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;

This gives (and executes):

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

Nick Krasnov · Answer 2 · 2016-12-21T15:34:05.430

You can avoid PL/SQL code and several dynamically constructed alter table statements. In order to disable all foreign keys that depend on a particular table's primary key, simply disable primary key with cascade clause and then re-enable(if you need to) it again.

Here is an example:

--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.

Disabling foreign keys:

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

Result:

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's not possible to enable all foreign key constraints again in cascade mode. It'd have to be done manually.

Crafty! But I'm assuming the enable won't be in "cascade" also, right? — Mojimi, Dec 21 '16 at 15:19
@Mojimi no. it will not be. you'll have to enable them manually. — Nick Krasnov, Dec 21 '16 at 15:21
does oracle allow to change target column if there is disabled foreign key to it? — Radoslav Ivanov, Oct 22 '19 at 03:30

Kacper · Answer 3 · 2016-12-21T15:17:37.750

3

Try that query to generate all alters you need:

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');

edited Dec 21 '16 at 15:17

answered Dec 21 '16 at 15:04

Kacper

4,798
2
19
34

1

This will give alter statements on MY_TABLE, while I believe the OP needs to disable the constraints referring to MY_TABLE – Aleksej Dec 21 '16 at 15:13

score -1 · Answer 4 · edited Mar 18 '20 at 16:30

-1

You must put the name of the table into

select 'alter table '||table_name||' disable constraint '|| constraint_name||'; 'from user_constraint
where r_constraint_name in
(select constraint_name
    from user_constraints
    where table_name='TCLIENTSALBARANS'
    and constraint_type='P');

edited Mar 18 '20 at 16:30

ionpoint

861
6
10

answered Mar 18 '20 at 15:58

Xavi Alavedra

1

Disable all foreign key constraints associated to specific table

4 Answers4