Revision 1329
Added by Aaron Marcuse-Kubitza about 12 years ago
| vegbien.sql | ||
|---|---|---|
| 1604 | 1604 |
CREATE TABLE plantconcept ( |
| 1605 | 1605 |
plantconcept_id integer NOT NULL, |
| 1606 | 1606 |
parent_id integer, |
| 1607 |
scope_id integer, |
|
| 1608 | 1607 |
plantname_id integer NOT NULL, |
| 1609 | 1608 |
plantcode text, |
| 1610 | 1609 |
plantdescription text, |
| ... | ... | |
| 1735 | 1734 |
|
| 1736 | 1735 |
CREATE TABLE plantname ( |
| 1737 | 1736 |
plantname_id integer NOT NULL, |
| 1737 |
scope_id integer, |
|
| 1738 |
rank taxonrank NOT NULL, |
|
| 1738 | 1739 |
plantname text NOT NULL, |
| 1739 |
rank taxonrank NOT NULL
|
|
| 1740 |
accessioncode text
|
|
| 1740 | 1741 |
); |
| 1741 | 1742 |
|
| 1742 | 1743 |
|
| ... | ... | |
| 4346 | 4347 |
|
| 4347 | 4348 |
|
| 4348 | 4349 |
-- |
| 4349 |
-- Name: fki_plantconcept_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4350 |
-- |
|
| 4351 |
|
|
| 4352 |
CREATE INDEX fki_plantconcept_scope_id ON plantconcept USING btree (scope_id); |
|
| 4353 |
|
|
| 4354 |
|
|
| 4355 |
-- |
|
| 4356 | 4350 |
-- Name: fki_plantconceptscope_locationevent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4357 | 4351 |
-- |
| 4358 | 4352 |
|
| ... | ... | |
| 4374 | 4368 |
|
| 4375 | 4369 |
|
| 4376 | 4370 |
-- |
| 4371 |
-- Name: fki_plantname_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4372 |
-- |
|
| 4373 |
|
|
| 4374 |
CREATE INDEX fki_plantname_scope_id ON plantname USING btree (scope_id); |
|
| 4375 |
|
|
| 4376 |
|
|
| 4377 |
-- |
|
| 4377 | 4378 |
-- Name: fki_plantobservation_plant_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4378 | 4379 |
-- |
| 4379 | 4380 |
|
| ... | ... | |
| 4727 | 4728 |
-- Name: plantconcept_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4728 | 4729 |
-- |
| 4729 | 4730 |
|
| 4730 |
CREATE UNIQUE INDEX plantconcept_unique ON plantconcept USING btree ((COALESCE(parent_id, 0)), (COALESCE(scope_id, 0)), plantname_id);
|
|
| 4731 |
CREATE UNIQUE INDEX plantconcept_unique ON plantconcept USING btree ((COALESCE(parent_id, 0)), plantname_id); |
|
| 4731 | 4732 |
|
| 4732 | 4733 |
|
| 4733 | 4734 |
-- |
| ... | ... | |
| 5656 | 5657 |
|
| 5657 | 5658 |
|
| 5658 | 5659 |
-- |
| 5659 |
-- Name: plantconcept_scope_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5660 |
-- |
|
| 5661 |
|
|
| 5662 |
ALTER TABLE ONLY plantconcept |
|
| 5663 |
ADD CONSTRAINT plantconcept_scope_id FOREIGN KEY (scope_id) REFERENCES plantconceptscope(plantconceptscope_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5664 |
|
|
| 5665 |
|
|
| 5666 |
-- |
|
| 5667 | 5660 |
-- Name: plantconceptscope_locationevent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5668 | 5661 |
-- |
| 5669 | 5662 |
|
| ... | ... | |
| 5720 | 5713 |
|
| 5721 | 5714 |
|
| 5722 | 5715 |
-- |
| 5716 |
-- Name: plantname_scope_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5717 |
-- |
|
| 5718 |
|
|
| 5719 |
ALTER TABLE ONLY plantname |
|
| 5720 |
ADD CONSTRAINT plantname_scope_id FOREIGN KEY (scope_id) REFERENCES plantconceptscope(plantconceptscope_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5721 |
|
|
| 5722 |
|
|
| 5723 |
-- |
|
| 5723 | 5724 |
-- Name: plantobservation_plant_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5724 | 5725 |
-- |
| 5725 | 5726 |
|
Also available in: Unified diff
vegbien.sql: Moved scope_id from plantconcept to plantname, since plantnames themselves are scoped, not just the plantconcepts that use them (e.g. "sp. 1" has different meanings in different scopes, so it should not be shared between scopes). plantname: Added accessioncode.