Revision 10457
Added by Aaron Marcuse-Kubitza almost 11 years ago
| VegCore.my.sql | ||
|---|---|---|
| 169 | 169 |
KEY `fk_event1_idx` (`parent`), |
| 170 | 170 |
KEY `fk_event_method1_idx` (`method`), |
| 171 | 171 |
KEY `fk_event_party_list1_idx` (`participants`), |
| 172 |
CONSTRAINT `fk_event_record1` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 173 |
CONSTRAINT `fk_event_place1` FOREIGN KEY (`place`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 172 | 174 |
CONSTRAINT `fk_event_party_list1` FOREIGN KEY (`participants`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 173 | 175 |
CONSTRAINT `fk_event1` FOREIGN KEY (`parent`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 174 |
CONSTRAINT `fk_event_method1` FOREIGN KEY (`method`) REFERENCES `method` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 175 |
CONSTRAINT `fk_event_place1` FOREIGN KEY (`place`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 176 |
CONSTRAINT `fk_event_record1` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 176 |
CONSTRAINT `fk_event_method1` FOREIGN KEY (`method`) REFERENCES `method` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 177 | 177 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"an action that occurs at a place and during a period of time" ("DwC":http://rs.tdwg.org/dwc/terms/#Event)';
|
| 178 | 178 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 179 | 179 |
|
| ... | ... | |
| 264 | 264 |
`traits` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
| 265 | 265 |
PRIMARY KEY (`id`), |
| 266 | 266 |
KEY `fk_individual_observation_individual1_idx` (`individual`), |
| 267 |
CONSTRAINT `fk_individual_observation_individual1` FOREIGN KEY (`individual`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 268 |
CONSTRAINT `fk_individual_observation_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 267 |
CONSTRAINT `fk_individual_observation_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 268 |
CONSTRAINT `fk_individual_observation_individual1` FOREIGN KEY (`individual`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 269 | 269 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An observation of an [[VegCore#Individual|Individual]]'; |
| 270 | 270 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 271 | 271 |
|
| ... | ... | |
| 358 | 358 |
`info` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
| 359 | 359 |
PRIMARY KEY (`id`), |
| 360 | 360 |
KEY `fk_party_organization1_idx` (`organization`), |
| 361 |
CONSTRAINT `fk_party_organization1` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 362 |
CONSTRAINT `fk_collection_source10` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 361 |
CONSTRAINT `fk_collection_source10` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 362 |
CONSTRAINT `fk_party_organization1` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 363 | 363 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; |
| 364 | 364 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 365 | 365 |
|
| ... | ... | |
| 703 | 703 |
KEY `fk_specimen_collection2_idx` (`current_collection`), |
| 704 | 704 |
KEY `fk_specimen_organization3_idx` (`owner_collection`), |
| 705 | 705 |
KEY `fk_specimen_party_list1_idx` (`specimenholder_institutions`), |
| 706 |
CONSTRAINT `fk_specimen_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 707 |
CONSTRAINT `fk_specimen_collection1` FOREIGN KEY (`orig_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 706 | 708 |
CONSTRAINT `fk_specimen_organization3` FOREIGN KEY (`owner_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 707 | 709 |
CONSTRAINT `fk_specimen_taxon_observation1` FOREIGN KEY (`collection_event`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 708 | 710 |
CONSTRAINT `fk_specimen_individual1` FOREIGN KEY (`individual`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 709 | 711 |
CONSTRAINT `fk_specimen_collection2` FOREIGN KEY (`current_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 710 |
CONSTRAINT `fk_specimen_party_list1` FOREIGN KEY (`specimenholder_institutions`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 711 |
CONSTRAINT `fk_specimen_collection1` FOREIGN KEY (`orig_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 712 |
CONSTRAINT `fk_specimen_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 712 |
CONSTRAINT `fk_specimen_party_list1` FOREIGN KEY (`specimenholder_institutions`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 713 | 713 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A "part of a plant" ("Wikipedia":http://en.wikipedia.org/wiki/Specimen) which was collected from it';
|
| 714 | 714 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 715 | 715 |
|
| ... | ... | |
| 782 | 782 |
UNIQUE KEY `stem_observation_unique` (`individual_observation`,`stem`), |
| 783 | 783 |
KEY `fk_stem_observation_individual_observation1_idx` (`individual_observation`), |
| 784 | 784 |
KEY `fk_stem_observation_stem1_idx` (`stem`), |
| 785 |
CONSTRAINT `fk_stem_observation_stem1` FOREIGN KEY (`stem`) REFERENCES `stem` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 785 | 786 |
CONSTRAINT `fk_stem_observation_individual_observation1` FOREIGN KEY (`individual_observation`) REFERENCES `individual_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 786 |
CONSTRAINT `fk_stem_observation_stem1` FOREIGN KEY (`stem`) REFERENCES `stem` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 787 | 787 |
CONSTRAINT `fk_stem_observation_individual_observation2` FOREIGN KEY (`id`) REFERENCES `individual_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
| 788 | 788 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An observation of a [[VegCore#Stem|Stem]]'; |
| 789 | 789 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| ... | ... | |
| 840 | 840 |
/*!40000 ALTER TABLE `subplot` ENABLE KEYS */; |
| 841 | 841 |
|
| 842 | 842 |
-- |
| 843 |
-- Table structure for table `taxon_absence` |
|
| 844 |
-- |
|
| 845 |
|
|
| 846 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 847 |
/*!40101 SET character_set_client = utf8 */; |
|
| 848 |
CREATE TABLE `taxon_absence` ( |
|
| 849 |
`id` varbinary(767) NOT NULL, |
|
| 850 |
`taxon_concept` varbinary(767) NOT NULL, |
|
| 851 |
PRIMARY KEY (`id`), |
|
| 852 |
KEY `fk_taxon_presence_taxon_name1_idx` (`taxon_concept`), |
|
| 853 |
CONSTRAINT `fk_taxon_observation_taxon_occurrence10` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 854 |
CONSTRAINT `fk_taxon_presence_taxon_name10` FOREIGN KEY (`taxon_concept`) REFERENCES `taxon_concept` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 855 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An observation that a [[VegCore#Taxon|Taxon]]''s does _not_ exist in a place'; |
|
| 856 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 857 |
|
|
| 858 |
-- |
|
| 859 |
-- Dumping data for table `taxon_absence` |
|
| 860 |
-- |
|
| 861 |
|
|
| 862 |
/*!40000 ALTER TABLE `taxon_absence` DISABLE KEYS */; |
|
| 863 |
/*!40000 ALTER TABLE `taxon_absence` ENABLE KEYS */; |
|
| 864 |
|
|
| 865 |
-- |
|
| 843 | 866 |
-- Table structure for table `taxon_assertion` |
| 844 | 867 |
-- |
| 845 | 868 |
|
| ... | ... | |
| 972 | 995 |
KEY `fk_taxon_observation_taxon_occurrence2_idx` (`taxon_occurrence`), |
| 973 | 996 |
KEY `fk_taxon_observation_specimen1_idx` (`voucher`), |
| 974 | 997 |
KEY `fk_taxon_observation_party_list1_idx` (`collectors`), |
| 998 |
CONSTRAINT `fk_taxon_observation_taxon_occurrence2` FOREIGN KEY (`taxon_occurrence`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 999 |
CONSTRAINT `fk_taxon_observation_party_list1` FOREIGN KEY (`collectors`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 975 | 1000 |
CONSTRAINT `fk_taxon_observation_event1` FOREIGN KEY (`id`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 976 |
CONSTRAINT `fk_taxon_observation_specimen1` FOREIGN KEY (`voucher`) REFERENCES `specimen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 977 |
CONSTRAINT `fk_taxon_observation_party_list1` FOREIGN KEY (`collectors`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 978 |
CONSTRAINT `fk_taxon_observation_taxon_occurrence2` FOREIGN KEY (`taxon_occurrence`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 1001 |
CONSTRAINT `fk_taxon_observation_specimen1` FOREIGN KEY (`voucher`) REFERENCES `specimen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 979 | 1002 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; |
| 980 | 1003 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 981 | 1004 |
|
Also available in: Unified diff
schemas/VegCore/VegCore.ERD.mwb: added taxon_absence, to avoid including absence observations in the same table as presence observations (which needlessly complicates queries). note that the fkey order now gets set back to forwards whenever a table is changed.