Revision 586
Added by Aaron Marcuse-Kubitza almost 13 years ago
| vegbien.for_ERD.my.sql | ||
|---|---|---|
| 151 | 151 |
authoreventcode character varying(30), |
| 152 | 152 |
`... (truncated) ...` int(11), |
| 153 | 153 |
accessioncode character varying(255), |
| 154 |
sourceaccessioncode character varying(100), |
|
| 154 | 155 |
CONSTRAINT locationevent_pkey PRIMARY KEY (locationevent_id ), |
| 155 | 156 |
CONSTRAINT locationevent_location_id FOREIGN KEY (location_id) |
| 156 | 157 |
REFERENCES location (location_id) MATCH SIMPLE |
| ... | ... | |
| 158 | 159 |
CONSTRAINT locationevent_project_id FOREIGN KEY (project_id) |
| 159 | 160 |
REFERENCES project (project_id) MATCH SIMPLE |
| 160 | 161 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 161 |
CONSTRAINT locationevent_keys UNIQUE (location_id , project_id , authoreventcode ) |
|
| 162 |
CONSTRAINT locationevent_keys_accessioncode UNIQUE (location_id , project_id , sourceaccessioncode ), |
|
| 163 |
CONSTRAINT locationevent_keys_code UNIQUE (location_id , project_id , authoreventcode ) |
|
| 162 | 164 |
); |
| 163 | 165 |
|
| 164 | 166 |
CREATE TABLE taxonoccurrence -- VegBank's taxonobservation table. |
| ... | ... | |
| 195 | 197 |
covercode character varying(10), |
| 196 | 198 |
count int(11) NOT NULL, |
| 197 | 199 |
accessioncode character varying(255), |
| 200 |
sourceaccessioncode character varying(100), |
|
| 198 | 201 |
CONSTRAINT aggregateoccurrence_pkey PRIMARY KEY (aggregateoccurrence_id ), |
| 199 | 202 |
CONSTRAINT aggregateoccurrence_taxonbinmethod_id FOREIGN KEY (taxonbinmethod_id) |
| 200 | 203 |
REFERENCES taxonbinmethod (taxonbinmethod_id) MATCH SIMPLE |
| ... | ... | |
| 202 | 205 |
CONSTRAINT aggregateoccurrence_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) |
| 203 | 206 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE |
| 204 | 207 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 205 |
CONSTRAINT aggregateoccurrence_keys UNIQUE (taxonoccurrence_id , taxonbinmethod_id ) |
|
| 208 |
CONSTRAINT aggregateoccurrence_keys_accessioncode UNIQUE (taxonoccurrence_id , sourceaccessioncode ), |
|
| 209 |
CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id , taxonbinmethod_id ) |
|
| 206 | 210 |
); |
| 207 | 211 |
|
| 208 | 212 |
CREATE TABLE individualplant -- VegBank's stemcount table. |
| ... | ... | |
| 215 | 219 |
authorplantcode character varying(20), |
| 216 | 220 |
accessioncode character varying(255), |
| 217 | 221 |
stemcount int(11), |
| 222 |
sourceaccessioncode character varying(100), |
|
| 218 | 223 |
CONSTRAINT individualplant_pkey PRIMARY KEY (individualplant_id ), |
| 219 | 224 |
CONSTRAINT individualplant_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id) |
| 220 | 225 |
REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE |
| 221 | 226 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 222 |
CONSTRAINT individualplant_keys UNIQUE (aggregateoccurrence_id , authorplantcode ) |
|
| 227 |
CONSTRAINT individualplant_keys_accessioncode UNIQUE (aggregateoccurrence_id , sourceaccessioncode ), |
|
| 228 |
CONSTRAINT individualplant_keys_code UNIQUE (aggregateoccurrence_id , authorplantcode ) |
|
| 223 | 229 |
); |
| 224 | 230 |
|
| 225 | 231 |
CREATE TABLE stem -- VegBank's stemlocation table. |
| ... | ... | |
| 237 | 243 |
age double precision, |
| 238 | 244 |
accessioncode character varying(255), |
| 239 | 245 |
diameteraccuracy double precision, |
| 246 |
sourceaccessioncode character varying(100), |
|
| 240 | 247 |
CONSTRAINT stem_pkey PRIMARY KEY (stem_id ), |
| 241 | 248 |
CONSTRAINT stem_individualplant_id FOREIGN KEY (individualplant_id) |
| 242 | 249 |
REFERENCES individualplant (individualplant_id) MATCH SIMPLE |
| 243 | 250 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 244 |
CONSTRAINT stem_keys UNIQUE (individualplant_id , authorstemcode ) |
|
| 251 |
CONSTRAINT stem_keys_accessioncode UNIQUE (individualplant_id , sourceaccessioncode ), |
|
| 252 |
CONSTRAINT stem_keys_code UNIQUE (individualplant_id , authorstemcode ) |
|
| 245 | 253 |
); |
| 246 | 254 |
|
| 247 | 255 |
CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data. |
| ... | ... | |
| 253 | 261 |
collectiondate timestamp NULL, |
| 254 | 262 |
collector_id int(11), |
| 255 | 263 |
museum_id int(11), |
| 256 |
sourceaccessionnumber character varying(100),
|
|
| 264 |
sourceaccessioncode character varying(100),
|
|
| 257 | 265 |
accessioncode character varying(255), |
| 258 | 266 |
taxonoccurrence_id int(11) NOT NULL, |
| 259 | 267 |
collectornumber_dwc character varying(255), |
| ... | ... | |
| 270 | 278 |
CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) |
| 271 | 279 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE |
| 272 | 280 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 273 |
CONSTRAINT specimen_keys UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ) |
|
| 281 |
CONSTRAINT specimen_keys_accessioncode UNIQUE (reference_id , collectioncode_dwc , sourceaccessioncode ), |
|
| 282 |
CONSTRAINT specimen_keys_catalognumber UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ) |
|
| 274 | 283 |
); |
| 275 | 284 |
|
| 276 | 285 |
CREATE TABLE voucher |
Also available in: Unified diff
VegBIEN: Renamed sourceaccessionnumber to sourceaccessioncode to show that they are the data source's analog of accessioncode. Added sourceaccessioncode to all applicable tables because this is the database pkey, which is distinct from any author*code applied by the collector.