SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

ALTER TABLE `default_schema`.`bien_genus_family` ENGINE = InnoDB ;

ALTER TABLE `default_schema`.`clade` ENGINE = InnoDB ;

ALTER TABLE `default_schema`.`cladeName` ENGINE = InnoDB ;

ALTER TABLE `default_schema`.`geoIndividualObservation` CHANGE COLUMN `ID` `ID` INT(11) NOT NULL COMMENT 'Links 1:1 with DBPlotID'  , 
  ADD CONSTRAINT `geoindividualobservation_ibfk_1`
  FOREIGN KEY (`ID` )
  REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `default_schema`.`geoPlotMetaDataDimension` CHANGE COLUMN `ID` `ID` INT(11) NOT NULL COMMENT 'Links 1:1 with DBPlotID'  , 
  ADD CONSTRAINT `geoplotmetadatadimension_ibfk_1`
  FOREIGN KEY (`ID` )
  REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `default_schema`.`IndividualObservation` 
  ADD CONSTRAINT `individualobservation_ibfk_3`
  FOREIGN KEY (`TaxonomyID` )
  REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `individualobservation_ibfk_1`
  FOREIGN KEY (`ObservationSpecimenID` )
  REFERENCES `default_schema`.`ObservationSpecimen` (`ObservationSpecimenID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `individualobservation_ibfk_2`
  FOREIGN KEY (`DBPlotID` )
  REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `default_schema`.`IndividualObservationMeasurement` ENGINE = InnoDB , ADD COLUMN `new` VARCHAR(45) NULL DEFAULT NULL  AFTER `test` 
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`StemNO`) 
, ADD UNIQUE INDEX `ObservationID` (`ObservationID` ASC, `StemNO` ASC, `ObservationNO` ASC) ;

ALTER TABLE `default_schema`.`ObservationSpecimen` 
  ADD CONSTRAINT `observationspecimen_ibfk_1`
  FOREIGN KEY (`DBSourceID` )
  REFERENCES `default_schema`.`SpecimenSourceData` (`DBSourceID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `default_schema`.`PlotAggregateFact` 
  ADD CONSTRAINT `plotaggregatefact_ibfk_3`
  FOREIGN KEY (`TaxonomyID` )
  REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `plotaggregatefact_ibfk_1`
  FOREIGN KEY (`DBPlotID` )
  REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `plotaggregatefact_ibfk_2`
  FOREIGN KEY (`PlotTimeID` )
  REFERENCES `default_schema`.`PlotTimeDimension` (`PlotTimeID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE
, DROP INDEX `DBPlotID_4` 
, DROP INDEX `DBPlotID_3` 
, DROP INDEX `DBPlotID_2` ;

ALTER TABLE `default_schema`.`PlotTimeDimension` 
  ADD CONSTRAINT `plottimedimension_ibfk_1`
  FOREIGN KEY (`DBPlotID` )
  REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE
, DROP INDEX `CensusName_8` 
, DROP INDEX `CensusName_7` 
, DROP INDEX `CensusName_6` 
, DROP INDEX `CensusName_4` 
, DROP INDEX `CensusName_3` ;

ALTER TABLE `default_schema`.`TaxonDataSource` CHANGE COLUMN `TaxonomyID` `TaxonomyID` INT(11) NOT NULL  , 
  ADD CONSTRAINT `TaxonDataSource_ibfk_2`
  FOREIGN KEY (`TaxonomyID` )
  REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `TaxonDataSource_ibfk_1`
  FOREIGN KEY (`DataSourceID` )
  REFERENCES `default_schema`.`DataSource` (`DataSourceID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `default_schema`.`TaxonObservation` 
  ADD CONSTRAINT `taxonobservation_ibfk_3`
  FOREIGN KEY (`TaxonomyID` )
  REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `taxonobservation_ibfk_1`
  FOREIGN KEY (`DBPlotID` )
  REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `taxonobservation_ibfk_2`
  FOREIGN KEY (`DBSourceID` )
  REFERENCES `default_schema`.`SpecimenSourceData` (`DBSourceID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE
, ADD INDEX `DBSourceID` (`DBSourceID` ASC) 
, DROP INDEX `TaxonomyID_4` 
, DROP INDEX `TaxonomyID_3` ;

ALTER TABLE `default_schema`.`TraitObservation` CHANGE COLUMN `TaxonomyID` `TaxonomyID` INT(11) NOT NULL  , 
  ADD CONSTRAINT `traitobservation_ibfk_1`
  FOREIGN KEY (`TaxonomyID` )
  REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `default_schema`.`viewFullOccurrence` ENGINE = InnoDB , CHANGE COLUMN `DBPlotID` `DBPlotID` INT(11) NOT NULL  , CHANGE COLUMN `TaxonomyID` `TaxonomyID` INT(11) NOT NULL  ;

DROP TABLE IF EXISTS `default_schema`.`viewFullOccurrence_bak20110825` ;

DROP TABLE IF EXISTS `default_schema`.`TaxonDimension_bak` ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;