Revision 1332
Added by Aaron Marcuse-Kubitza almost 13 years ago
| vegbien.sql | ||
|---|---|---|
| 1603 | 1603 |
|
| 1604 | 1604 |
CREATE TABLE plantconcept ( |
| 1605 | 1605 |
plantconcept_id integer NOT NULL, |
| 1606 |
parent_id integer, |
|
| 1607 | 1606 |
plantname_id integer NOT NULL, |
| 1608 | 1607 |
plantcode text, |
| 1609 | 1608 |
plantdescription text, |
| ... | ... | |
| 1734 | 1733 |
|
| 1735 | 1734 |
CREATE TABLE plantname ( |
| 1736 | 1735 |
plantname_id integer NOT NULL, |
| 1736 |
parent_id integer, |
|
| 1737 | 1737 |
scope_id integer, |
| 1738 | 1738 |
rank taxonrank NOT NULL, |
| 1739 | 1739 |
plantname text NOT NULL, |
| ... | ... | |
| 3662 | 3662 |
|
| 3663 | 3663 |
|
| 3664 | 3664 |
-- |
| 3665 |
-- Name: plantconcept_unique; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3666 |
-- |
|
| 3667 |
|
|
| 3668 |
ALTER TABLE ONLY plantconcept |
|
| 3669 |
ADD CONSTRAINT plantconcept_unique UNIQUE (plantname_id); |
|
| 3670 |
|
|
| 3671 |
|
|
| 3672 |
-- |
|
| 3665 | 3673 |
-- Name: plantconceptscope_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3666 | 3674 |
-- |
| 3667 | 3675 |
|
| ... | ... | |
| 3694 | 3702 |
|
| 3695 | 3703 |
|
| 3696 | 3704 |
-- |
| 3697 |
-- Name: plantname_unique; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3698 |
-- |
|
| 3699 |
|
|
| 3700 |
ALTER TABLE ONLY plantname |
|
| 3701 |
ADD CONSTRAINT plantname_unique UNIQUE (rank, plantname); |
|
| 3702 |
|
|
| 3703 |
|
|
| 3704 |
-- |
|
| 3705 | 3705 |
-- Name: plantobservation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3706 | 3706 |
-- |
| 3707 | 3707 |
|
| ... | ... | |
| 4340 | 4340 |
|
| 4341 | 4341 |
|
| 4342 | 4342 |
-- |
| 4343 |
-- Name: fki_plantconcept_parent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4344 |
-- |
|
| 4345 |
|
|
| 4346 |
CREATE INDEX fki_plantconcept_parent_id ON plantconcept USING btree (parent_id); |
|
| 4347 |
|
|
| 4348 |
|
|
| 4349 |
-- |
|
| 4350 | 4343 |
-- Name: fki_plantconceptscope_locationevent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4351 | 4344 |
-- |
| 4352 | 4345 |
|
| ... | ... | |
| 4368 | 4361 |
|
| 4369 | 4362 |
|
| 4370 | 4363 |
-- |
| 4364 |
-- Name: fki_plantname_parent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4365 |
-- |
|
| 4366 |
|
|
| 4367 |
CREATE INDEX fki_plantname_parent_id ON plantname USING btree (parent_id); |
|
| 4368 |
|
|
| 4369 |
|
|
| 4370 |
-- |
|
| 4371 | 4371 |
-- Name: fki_plantname_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4372 | 4372 |
-- |
| 4373 | 4373 |
|
| ... | ... | |
| 4725 | 4725 |
|
| 4726 | 4726 |
|
| 4727 | 4727 |
-- |
| 4728 |
-- Name: plantconcept_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4729 |
-- |
|
| 4730 |
|
|
| 4731 |
CREATE UNIQUE INDEX plantconcept_unique ON plantconcept USING btree ((COALESCE(parent_id, 0)), plantname_id); |
|
| 4732 |
|
|
| 4733 |
|
|
| 4734 |
-- |
|
| 4735 | 4728 |
-- Name: plantconceptscope_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4736 | 4729 |
-- |
| 4737 | 4730 |
|
| ... | ... | |
| 4781 | 4774 |
|
| 4782 | 4775 |
|
| 4783 | 4776 |
-- |
| 4777 |
-- Name: plantname_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4778 |
-- |
|
| 4779 |
|
|
| 4780 |
CREATE UNIQUE INDEX plantname_unique ON plantname USING btree ((COALESCE(parent_id, 0)), (COALESCE(scope_id, 0)), rank, plantname); |
|
| 4781 |
|
|
| 4782 |
|
|
| 4783 |
-- |
|
| 4784 | 4784 |
-- Name: plantstatus_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4785 | 4785 |
-- |
| 4786 | 4786 |
|
| ... | ... | |
| 5641 | 5641 |
|
| 5642 | 5642 |
|
| 5643 | 5643 |
-- |
| 5644 |
-- Name: plantconcept_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5645 |
-- |
|
| 5646 |
|
|
| 5647 |
ALTER TABLE ONLY plantconcept |
|
| 5648 |
ADD CONSTRAINT plantconcept_parent_id FOREIGN KEY (parent_id) REFERENCES plantconcept(plantconcept_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5649 |
|
|
| 5650 |
|
|
| 5651 |
-- |
|
| 5652 | 5644 |
-- Name: plantconcept_plantname_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5653 | 5645 |
-- |
| 5654 | 5646 |
|
| ... | ... | |
| 5713 | 5705 |
|
| 5714 | 5706 |
|
| 5715 | 5707 |
-- |
| 5708 |
-- Name: plantname_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5709 |
-- |
|
| 5710 |
|
|
| 5711 |
ALTER TABLE ONLY plantname |
|
| 5712 |
ADD CONSTRAINT plantname_parent_id FOREIGN KEY (parent_id) REFERENCES plantname(plantname_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5713 |
|
|
| 5714 |
|
|
| 5715 |
-- |
|
| 5716 | 5716 |
-- Name: plantname_scope_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5717 | 5717 |
-- |
| 5718 | 5718 |
|
Also available in: Unified diff
vegbien.sql: Moved parent_id from plantconcept to plantname, since plantnames themselves are unique according to their parent taxons (a species under one genus is not the same as a species under another genus)