Revision 10457
Added by Aaron Marcuse-Kubitza almost 11 years ago
| VegCore.pg.sql | ||
|---|---|---|
| 171 | 171 |
/*KEY "fk_event1_idx" ("parent")*/CHECK (true),
|
| 172 | 172 |
/*KEY "fk_event_method1_idx" ("method")*/CHECK (true),
|
| 173 | 173 |
/*KEY "fk_event_party_list1_idx" ("participants")*/CHECK (true),
|
| 174 |
/*CONSTRAINT "fk_event_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 175 |
/*CONSTRAINT "fk_event_place1" FOREIGN KEY ("place") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 174 | 176 |
/*CONSTRAINT "fk_event_party_list1" FOREIGN KEY ("participants") REFERENCES "party_list" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 175 | 177 |
/*CONSTRAINT "fk_event1" FOREIGN KEY ("parent") REFERENCES "event" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 176 |
/*CONSTRAINT "fk_event_method1" FOREIGN KEY ("method") REFERENCES "method" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 177 |
/*CONSTRAINT "fk_event_place1" FOREIGN KEY ("place") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 178 |
/*CONSTRAINT "fk_event_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 178 |
/*CONSTRAINT "fk_event_method1" FOREIGN KEY ("method") REFERENCES "method" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 179 | 179 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"an action that occurs at a place and during a period of text/*time*/" ("DwC":http://rs.tdwg.org/dwc/terms/#Event)'*/;
|
| 180 | 180 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 181 | 181 |
|
| ... | ... | |
| 266 | 266 |
"traits" hstore DEFAULT NULL, |
| 267 | 267 |
PRIMARY KEY ("id"),
|
| 268 | 268 |
/*KEY "fk_individual_observation_individual1_idx" ("individual")*/CHECK (true),
|
| 269 |
/*CONSTRAINT "fk_individual_observation_individual1" FOREIGN KEY ("individual") REFERENCES "individual" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 270 |
/*CONSTRAINT "fk_individual_observation_taxon_occurrence1" FOREIGN KEY ("id") REFERENCES "taxon_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 269 |
/*CONSTRAINT "fk_individual_observation_taxon_occurrence1" FOREIGN KEY ("id") REFERENCES "taxon_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 270 |
/*CONSTRAINT "fk_individual_observation_individual1" FOREIGN KEY ("individual") REFERENCES "individual" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 271 | 271 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An observation of an [[VegCore#Individual|Individual]]'*/; |
| 272 | 272 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 273 | 273 |
|
| ... | ... | |
| 360 | 360 |
"info" hstore DEFAULT NULL, |
| 361 | 361 |
PRIMARY KEY ("id"),
|
| 362 | 362 |
/*KEY "fk_party_organization1_idx" ("organization")*/CHECK (true),
|
| 363 |
/*CONSTRAINT "fk_party_organization1" FOREIGN KEY ("organization") REFERENCES "organization" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 364 |
/*CONSTRAINT "fk_collection_source10" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 363 |
/*CONSTRAINT "fk_collection_source10" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 364 |
/*CONSTRAINT "fk_party_organization1" FOREIGN KEY ("organization") REFERENCES "organization" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 365 | 365 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin*/; |
| 366 | 366 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 367 | 367 |
|
| ... | ... | |
| 705 | 705 |
/*KEY "fk_specimen_collection2_idx" ("current_collection")*/CHECK (true),
|
| 706 | 706 |
/*KEY "fk_specimen_organization3_idx" ("owner_collection")*/CHECK (true),
|
| 707 | 707 |
/*KEY "fk_specimen_party_list1_idx" ("specimenholder_institutions")*/CHECK (true),
|
| 708 |
/*CONSTRAINT "fk_specimen_taxon_occurrence1" FOREIGN KEY ("id") REFERENCES "taxon_occurrence" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 709 |
/*CONSTRAINT "fk_specimen_collection1" FOREIGN KEY ("orig_collection") REFERENCES "collection" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 708 | 710 |
/*CONSTRAINT "fk_specimen_organization3" FOREIGN KEY ("owner_collection") REFERENCES "collection" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 709 | 711 |
/*CONSTRAINT "fk_specimen_taxon_observation1" FOREIGN KEY ("collection_event") REFERENCES "taxon_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 710 | 712 |
/*CONSTRAINT "fk_specimen_individual1" FOREIGN KEY ("individual") REFERENCES "individual" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 711 | 713 |
/*CONSTRAINT "fk_specimen_collection2" FOREIGN KEY ("current_collection") REFERENCES "collection" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 712 |
/*CONSTRAINT "fk_specimen_party_list1" FOREIGN KEY ("specimenholder_institutions") REFERENCES "party_list" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 713 |
/*CONSTRAINT "fk_specimen_collection1" FOREIGN KEY ("orig_collection") REFERENCES "collection" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 714 |
/*CONSTRAINT "fk_specimen_taxon_occurrence1" FOREIGN KEY ("id") REFERENCES "taxon_occurrence" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 714 |
/*CONSTRAINT "fk_specimen_party_list1" FOREIGN KEY ("specimenholder_institutions") REFERENCES "party_list" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 715 | 715 |
) /*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'*/;
|
| 716 | 716 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 717 | 717 |
|
| ... | ... | |
| 784 | 784 |
/*CONSTRAINT "stem_observation_unique" */UNIQUE ("individual_observation","stem"),
|
| 785 | 785 |
/*KEY "fk_stem_observation_individual_observation1_idx" ("individual_observation")*/CHECK (true),
|
| 786 | 786 |
/*KEY "fk_stem_observation_stem1_idx" ("stem")*/CHECK (true),
|
| 787 |
/*CONSTRAINT "fk_stem_observation_stem1" FOREIGN KEY ("stem") REFERENCES "stem" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 787 | 788 |
/*CONSTRAINT "fk_stem_observation_individual_observation1" FOREIGN KEY ("individual_observation") REFERENCES "individual_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 788 |
/*CONSTRAINT "fk_stem_observation_stem1" FOREIGN KEY ("stem") REFERENCES "stem" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 789 | 789 |
/*CONSTRAINT "fk_stem_observation_individual_observation2" FOREIGN KEY ("id") REFERENCES "individual_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
| 790 | 790 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An observation of a [[VegCore#Stem|Stem]]'*/; |
| 791 | 791 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| ... | ... | |
| 842 | 842 |
/*!40000 ALTER TABLE "subplot" ENABLE KEYS */; |
| 843 | 843 |
|
| 844 | 844 |
-- |
| 845 |
-- Table structure for table "taxon_absence" |
|
| 846 |
-- |
|
| 847 |
|
|
| 848 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 849 |
/*!40101 SET character_set_client = utf8 */; |
|
| 850 |
CREATE TABLE "taxon_absence" ( |
|
| 851 |
"id" text NOT NULL, |
|
| 852 |
"taxon_concept" text NOT NULL, |
|
| 853 |
PRIMARY KEY ("id"),
|
|
| 854 |
/*KEY "fk_taxon_presence_taxon_name1_idx" ("taxon_concept")*/CHECK (true),
|
|
| 855 |
/*CONSTRAINT "fk_taxon_observation_taxon_occurrence10" FOREIGN KEY ("id") REFERENCES "taxon_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 856 |
/*CONSTRAINT "fk_taxon_presence_taxon_name10" FOREIGN KEY ("taxon_concept") REFERENCES "taxon_concept" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 857 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An observation that a [[VegCore#Taxon|Taxon]]''s does _not_ exist in a place'*/; |
|
| 858 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 859 |
|
|
| 860 |
-- |
|
| 861 |
-- Dumping data for table "taxon_absence" |
|
| 862 |
-- |
|
| 863 |
|
|
| 864 |
/*!40000 ALTER TABLE "taxon_absence" DISABLE KEYS */; |
|
| 865 |
/*!40000 ALTER TABLE "taxon_absence" ENABLE KEYS */; |
|
| 866 |
|
|
| 867 |
-- |
|
| 845 | 868 |
-- Table structure for table "taxon_assertion" |
| 846 | 869 |
-- |
| 847 | 870 |
|
| ... | ... | |
| 974 | 997 |
/*KEY "fk_taxon_observation_taxon_occurrence2_idx" ("taxon_occurrence")*/CHECK (true),
|
| 975 | 998 |
/*KEY "fk_taxon_observation_specimen1_idx" ("voucher")*/CHECK (true),
|
| 976 | 999 |
/*KEY "fk_taxon_observation_party_list1_idx" ("collectors")*/CHECK (true),
|
| 1000 |
/*CONSTRAINT "fk_taxon_observation_taxon_occurrence2" FOREIGN KEY ("taxon_occurrence") REFERENCES "taxon_occurrence" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 1001 |
/*CONSTRAINT "fk_taxon_observation_party_list1" FOREIGN KEY ("collectors") REFERENCES "party_list" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 977 | 1002 |
/*CONSTRAINT "fk_taxon_observation_event1" FOREIGN KEY ("id") REFERENCES "event" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 978 |
/*CONSTRAINT "fk_taxon_observation_specimen1" FOREIGN KEY ("voucher") REFERENCES "specimen" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 979 |
/*CONSTRAINT "fk_taxon_observation_party_list1" FOREIGN KEY ("collectors") REFERENCES "party_list" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 980 |
/*CONSTRAINT "fk_taxon_observation_taxon_occurrence2" FOREIGN KEY ("taxon_occurrence") REFERENCES "taxon_occurrence" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 1003 |
/*CONSTRAINT "fk_taxon_observation_specimen1" FOREIGN KEY ("voucher") REFERENCES "specimen" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 981 | 1004 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin*/; |
| 982 | 1005 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 983 | 1006 |
|
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.