How to enable and disable foreign key constraints in Oracle dynamically

posted Apr 21, 2012, 6:27 PM by Sachchida Ojha   [ updated Apr 21, 2012, 6:42 PM ]

-- List Constraint name


select constraint_name, table_name from user_constraints where constraint_type='R' and r_CONSTRAINT_NAME='DBAREF_EMP_PK' and status = 'ENABLED';

-- Disable them dynamically

begin
for i in (select constraint_name, table_name from user_constraints where constraint_type='R' and r_CONSTRAINT_NAME='DBAREF_EMP_PK' and status = 'ENABLED') LOOP
execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;

--Veryfy if all the constraint has been disabled - you will see 0 rows from below SQL
select constraint_name, table_name from user_constraints where constraint_type='R' and r_CONSTRAINT_NAME='DBAREF_EMP_PK' and status = 'DISABLED';

--this will enable them again

begin
for i in (select constraint_name, table_name from user_constraints where constraint_type='R' and r_CONSTRAINT_NAME='DBAREF_EMP_PK' and status = 'DISABLED') LOOP
execute immediate 'alter table '||i.table_name||' enable novalidate constraint '||i.constraint_name||'';
end loop;
end;
/

--Veryfy if all the constraint has been enabled

select constraint_name, table_name from user_constraints where constraint_type='R' and r_CONSTRAINT_NAME='DBAREF_EMP_PK' and status = 'ENABLED';



--this will disable all foreign key constraints
begin
for i in (select constraint_name, table_name from user_constraints where constraint_type ='R'
and status = 'ENABLED') LOOP
execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;

--this will enable them again
begin
for i in (select constraint_name, table_name from user_constraints where constraint_type ='R'
and status = 'DISABLED') LOOP
execute immediate 'alter table '||i.table_name||' enable constraint '||i.constraint_name||'';
end loop;
end;





Comments