Disabling foreign key constraints so you can delete records in Oracle

From time to time in Oracle we need to delete some records from a table which is referenced by foreign keys. Sometimes if you delete the records that refer to the records you are trying to delete you can delete them (if no records refer to those records primary keys). Other times even if no records refer to the records you are trying to delete you still will not be able to delete them just because of the presence of the foreign key. 

The workaround is after you verify that there are no records referring to the records you want to delete you can temporarily disable the foreign key constraint then delete your records then enable the constraints. To find the constraints run the following query after changing 'SOME_TABLE' to your table name:

select 'alter table '||a.owner||'.'||a.table_name||

' disable constraint '||a.constraint_name||'

/'

from all_constraints a, all_constraints b

where a.constraint_type = 'R'

and a.r_constraint_name = b.constraint_name

and a.r_owner = b.owner

and b.table_name = 'SOME_TABLE'

/

You can also specify the schema I usually just check the output before running for tables with the same name in the same schema.

If you then copy the sql generated from this query you then run it to disable the constraints. Then run you deletes. Afterward just change the keyword "disable" in the statements and change them to "enable" and run that to enable the constraints.

If you were correct that there were not records referencing the records through the foreign key the constraint will enable successfully. If it doesn't then you just need to find the corresponding records and remove them.


For example if you ran the above query and 4 tables had foreign keys to 'SOME_TABLE' then the above query might generate the following:

alter table SOME_SCHEMA.TABLE1 disable constraint SOME_FK

/ 

alter table SOME_SCHEMA.TABLE2 disable constraint SOME_OTHER_FK

/           

alter table SOME_SCHEMA.TABLE3 disable constraint SOME_OTHER2_FK

/   

alter table SOME_SCHEMA.TABLE4 disable constraint SOME_OTHER3_FK

/  

Run this then you will be able to successfully execute:

delete from SOME_SCHEMA.SOME_TABLE where ...

/  

Then change disable to enable and run that:

alter table SOME_SCHEMA.TABLE1 enable constraint SOME_FK

/ 

alter table SOME_SCHEMA.TABLE2 enable constraint SOME_OTHER_FK

/           

alter table SOME_SCHEMA.TABLE3 enable constraint SOME_OTHER2_FK

/   

alter table SOME_SCHEMA.TABLE4 enable constraint SOME_OTHER3_FK

/  

and your records are gone and your constraints are back up.  Just make sure you remove the records referring to the records you are trying to delete first!  Sometimes those records may not be deleted until some other constraints are disabled.  Follow the dependencies as deep as needed repeating this process for each table you can't delete from.