Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Disable all foreign key constraints associated to specific table

Tags:

sql

oracle

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)

like image 268
Mojimi Avatar asked Dec 21 '16 14:12

Mojimi


People also ask

How do I disable foreign key constraints?

To disable a foreign key constraint for INSERT and UPDATE statements. In Object Explorer, expand the table with the constraint and then expand the Keys folder. Right-click the constraint and select Modify. In the grid under Table Designer, select Enforce Foreign Key Constraint and select No from the drop-down menu.

How do I turn off all constraints?

The following command will disable all constraints on a table: ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL; Once data is imported you need to make sure the new data is valid according to the declarative rules in the database.

How do you disable all the referenced foreign keys and the primary or unique key?

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.


3 Answers

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
like image 102
Aleksej Avatar answered Oct 26 '22 22:10

Aleksej


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.

like image 28
Nick Krasnov Avatar answered Oct 26 '22 22:10

Nick Krasnov


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');
like image 30
Kacper Avatar answered Oct 26 '22 21:10

Kacper