Revision 1332
Added by Aaron Marcuse-Kubitza almost 13 years ago
| 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 |
|
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)