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)
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.
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.
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.
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
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.
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');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With