If you are trying to delete an EO, Managed Area, Reference, Conservation Site or other record, and you get an error message indicting that you can't delete the record because it is related to other information in the database, you can use the following steps to determine the offending record or records. The following set of queries uses Conservation Site as an example where a user is trying to delete a conservation site with a conservation_site_id = 986, but they are receiving an error message that the site they are trying to delete is related to another conservation site. If you need to query for a constraint related to another table, simply change the criteria next to "table_name".


In the example below, we will find out what is blocking Conservation Site with the ID 986 from being deleted. First, run a query to find all foreign key constraints that reference the table from which the record cannot be deleted. 

 

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = 'BIOTICS_USER'
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = 'CONSERVATION_SITE'
   and owner = 'BIOTICS_USER'
 )
order by table_name, constraint_name

Look for constraints that involve the table listed in the error message. In this case, only one constraint within the result set, SITE_MACRO_SITE, originates from CONSERVATION_SITE. The following query returns the columns involved in this constraint.

select * from all_cons_columns where constraint_name='SITE_MACRO_SITE';

This returns the column  "MACRO_SITE_ID". It is then easy to write a query using this column (MACRO_SITE_ID) and the ID of the record you were unable to delete (986) to find the record that is blocking its deletion.

select * from conservation_site where macro_site_id=986

The results show that Conservation Site #1718 is the offending record which blocks #986 from being deleted.