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;