# 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 tmpCountries 	select distinct	bioRecordCountryCode 	   from bioRecord;

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

# now, remove the orphans
create table tmpTaxon like taxon;
insert into tmpTaxon
select taxon.* from taxon
straight_join tmpTaxa on taxonKey = bioRecordTaxonKey;

create table tmpVernacularName like vernacularName;
insert into tmpVernacularName
select vernacularName.* from vernacularName
straight_join tmpTaxa on vernacularTaxonKey = bioRecordTaxonKey;

create table tmpHigherTaxon like higherTaxon;
insert into tmpHigherTaxon
select higherTaxon.* from higherTaxon
straight_join tmpTaxa on taxonKey = bioRecordTaxonKey;

create table tmpImageRecord like imageRecord;
insert into tmpImageRecord
select imageRecord.* from imageRecord
straight_join bioRecord on imageRecordBioRecordKey = bioRecordKey;

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

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

# Finish everything
optimize table resource, isoCountry;

drop tables taxon, vernacularName, higherTaxon, imageRecord;
rename table tmpTaxon to taxon, tmpVernacularName to vernacularName, tmpHigherTaxon to higherTaxon, tmpImageRecord to imageRecord;
