# create temporary tables with all existing objects
create temporary table tmpTaxa 				select distinct	bioRecordTaxonKey 			 from bioRecord;
create temporary table tmpResources 	select distinct	bioRecordResourceKey	 	 from bioRecord;
create temporary table tmpCollections select distinct	bioRecordCollectionKey	 from bioRecord;
create temporary table tmpCountries 	select distinct	bioRecordCountryCode 	   from bioRecord;

alter table tmpTaxa			    add key (bioRecordTaxonKey);
alter table tmpResources		add key (bioRecordResourceKey);
alter table tmpCollections	add key (bioRecordCollectionKey);
alter table tmpCountries		add key (bioRecordCountryCode);

# now, remove the orphans
delete from taxon
where taxonKey not in (select bioRecordTaxonKey from tmpTaxa);

delete from vernacularName
where vernacularTaxonKey not in (select bioRecordTaxonKey from tmpTaxa);

delete from higherTaxon
where taxonKey not in (select bioRecordTaxonKey from tmpTaxa);

delete from collection
where collectionKey not in (select bioRecordCollectionKey from tmpCollections);

delete from resource
where resourceKey not in (select bioRecordResourceKey from tmpResources);

delete from isoCountry
where isoCountryCode not in (select bioRecordCountryCode from tmpCountries);

delete imageRecord
from imageRecord
left join bioRecord on imageRecordBioRecordKey = bioRecordKey
where bioRecordKey is null;

