Revision 1332
Added by Aaron Marcuse-Kubitza almost 13 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 1346 | 1346 |
|
| 1347 | 1347 |
CREATE TABLE plantconcept ( |
| 1348 | 1348 |
plantconcept_id int(11) NOT NULL, |
| 1349 |
parent_id int(11), |
|
| 1350 | 1349 |
plantname_id int(11) NOT NULL, |
| 1351 | 1350 |
plantcode text, |
| 1352 | 1351 |
plantdescription text, |
| ... | ... | |
| 1457 | 1456 |
|
| 1458 | 1457 |
CREATE TABLE plantname ( |
| 1459 | 1458 |
plantname_id int(11) NOT NULL, |
| 1459 |
parent_id int(11), |
|
| 1460 | 1460 |
scope_id int(11), |
| 1461 | 1461 |
rank text NOT NULL, |
| 1462 | 1462 |
plantname text NOT NULL, |
| ... | ... | |
| 3250 | 3250 |
|
| 3251 | 3251 |
|
| 3252 | 3252 |
-- |
| 3253 |
-- Name: plantconcept_unique; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3254 |
-- |
|
| 3255 |
|
|
| 3256 |
ALTER TABLE plantconcept |
|
| 3257 |
ADD CONSTRAINT plantconcept_unique UNIQUE (plantname_id); |
|
| 3258 |
|
|
| 3259 |
|
|
| 3260 |
-- |
|
| 3253 | 3261 |
-- Name: plantconceptscope_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3254 | 3262 |
-- |
| 3255 | 3263 |
|
| ... | ... | |
| 3282 | 3290 |
|
| 3283 | 3291 |
|
| 3284 | 3292 |
-- |
| 3285 |
-- Name: plantname_unique; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3286 |
-- |
|
| 3287 |
|
|
| 3288 |
ALTER TABLE plantname |
|
| 3289 |
ADD CONSTRAINT plantname_unique UNIQUE (rank, plantname); |
|
| 3290 |
|
|
| 3291 |
|
|
| 3292 |
-- |
|
| 3293 | 3293 |
-- Name: plantobservation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3294 | 3294 |
-- |
| 3295 | 3295 |
|
| ... | ... | |
| 3928 | 3928 |
|
| 3929 | 3929 |
|
| 3930 | 3930 |
-- |
| 3931 |
-- Name: fki_plantconcept_parent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3932 |
-- |
|
| 3933 |
|
|
| 3934 |
CREATE INDEX fki_plantconcept_parent_id ON plantconcept (parent_id); |
|
| 3935 |
|
|
| 3936 |
|
|
| 3937 |
-- |
|
| 3938 | 3931 |
-- Name: fki_plantconceptscope_locationevent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3939 | 3932 |
-- |
| 3940 | 3933 |
|
| ... | ... | |
| 3956 | 3949 |
|
| 3957 | 3950 |
|
| 3958 | 3951 |
-- |
| 3952 |
-- Name: fki_plantname_parent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3953 |
-- |
|
| 3954 |
|
|
| 3955 |
CREATE INDEX fki_plantname_parent_id ON plantname (parent_id); |
|
| 3956 |
|
|
| 3957 |
|
|
| 3958 |
-- |
|
| 3959 | 3959 |
-- Name: fki_plantname_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3960 | 3960 |
-- |
| 3961 | 3961 |
|
| ... | ... | |
| 4313 | 4313 |
|
| 4314 | 4314 |
|
| 4315 | 4315 |
-- |
| 4316 |
-- Name: plantconcept_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4317 |
-- |
|
| 4318 |
|
|
| 4319 |
|
|
| 4320 |
|
|
| 4321 |
|
|
| 4322 |
-- |
|
| 4323 | 4316 |
-- Name: plantconceptscope_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4324 | 4317 |
-- |
| 4325 | 4318 |
|
| ... | ... | |
| 4369 | 4362 |
|
| 4370 | 4363 |
|
| 4371 | 4364 |
-- |
| 4365 |
-- Name: plantname_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4366 |
-- |
|
| 4367 |
|
|
| 4368 |
|
|
| 4369 |
|
|
| 4370 |
|
|
| 4371 |
-- |
|
| 4372 | 4372 |
-- Name: plantstatus_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4373 | 4373 |
-- |
| 4374 | 4374 |
|
| ... | ... | |
| 5211 | 5211 |
|
| 5212 | 5212 |
|
| 5213 | 5213 |
-- |
| 5214 |
-- Name: plantconcept_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5215 |
-- |
|
| 5216 |
|
|
| 5217 |
ALTER TABLE plantconcept |
|
| 5218 |
ADD CONSTRAINT plantconcept_parent_id FOREIGN KEY (parent_id) REFERENCES plantconcept(plantconcept_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5219 |
|
|
| 5220 |
|
|
| 5221 |
-- |
|
| 5222 | 5214 |
-- Name: plantconcept_plantname_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5223 | 5215 |
-- |
| 5224 | 5216 |
|
| ... | ... | |
| 5283 | 5275 |
|
| 5284 | 5276 |
|
| 5285 | 5277 |
-- |
| 5278 |
-- Name: plantname_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5279 |
-- |
|
| 5280 |
|
|
| 5281 |
ALTER TABLE plantname |
|
| 5282 |
ADD CONSTRAINT plantname_parent_id FOREIGN KEY (parent_id) REFERENCES plantname(plantname_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5283 |
|
|
| 5284 |
|
|
| 5285 |
-- |
|
| 5286 | 5286 |
-- Name: plantname_scope_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5287 | 5287 |
-- |
| 5288 | 5288 |
|
| schemas/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)