Revision 4699
Added by Aaron Marcuse-Kubitza about 12 years ago
| vegbien.sql | ||
|---|---|---|
| 906 | 906 |
|
| 907 | 907 |
CREATE TABLE plantconcept ( |
| 908 | 908 |
plantconcept_id integer NOT NULL, |
| 909 |
datasource_id integer NOT NULL, |
|
| 910 |
plantcode text, |
|
| 909 | 911 |
plantname_id integer NOT NULL, |
| 910 | 912 |
scientificname text, |
| 911 | 913 |
scientificnameauthor text, |
| ... | ... | |
| 922 | 924 |
variety text, |
| 923 | 925 |
forma text, |
| 924 | 926 |
cultivar text, |
| 925 |
plantcode text, |
|
| 926 | 927 |
accessioncode text |
| 927 | 928 |
); |
| 928 | 929 |
|
| ... | ... | |
| 3833 | 3834 |
|
| 3834 | 3835 |
|
| 3835 | 3836 |
-- |
| 3836 |
-- Name: plantconcept_unique; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3837 |
-- |
|
| 3838 |
|
|
| 3839 |
ALTER TABLE ONLY plantconcept |
|
| 3840 |
ADD CONSTRAINT plantconcept_unique UNIQUE (plantname_id); |
|
| 3841 |
|
|
| 3842 |
|
|
| 3843 |
-- |
|
| 3844 | 3837 |
-- Name: plantcorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3845 | 3838 |
-- |
| 3846 | 3839 |
|
| ... | ... | |
| 4326 | 4319 |
|
| 4327 | 4320 |
|
| 4328 | 4321 |
-- |
| 4322 |
-- Name: plantconcept_unique_within_datasource_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4323 |
-- |
|
| 4324 |
|
|
| 4325 |
CREATE UNIQUE INDEX plantconcept_unique_within_datasource_by_code ON plantconcept USING btree (datasource_id, (COALESCE(plantcode, '\\N'::text))) WHERE (plantcode IS NOT NULL); |
|
| 4326 |
|
|
| 4327 |
|
|
| 4328 |
-- |
|
| 4329 |
-- Name: plantconcept_unique_within_datasource_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4330 |
-- |
|
| 4331 |
|
|
| 4332 |
CREATE UNIQUE INDEX plantconcept_unique_within_datasource_by_name ON plantconcept USING btree (datasource_id, (COALESCE(plantname_id, 2147483647)), (COALESCE(scientificname, '\\N'::text)), (COALESCE(scientificnameauthor, '\\N'::text)), (COALESCE(scientificnamewithauthor, '\\N'::text)), (COALESCE(domain, '\\N'::text)), (COALESCE(kingdom, '\\N'::text)), (COALESCE(phylum, '\\N'::text)), (COALESCE(class, '\\N'::text)), (COALESCE("order", '\\N'::text)), (COALESCE(family, '\\N'::text)), (COALESCE(genus, '\\N'::text)), (COALESCE(species, '\\N'::text)), (COALESCE(subspecies, '\\N'::text)), (COALESCE(variety, '\\N'::text)), (COALESCE(forma, '\\N'::text)), (COALESCE(cultivar, '\\N'::text))) WHERE (plantcode IS NULL);
|
|
| 4333 |
|
|
| 4334 |
|
|
| 4335 |
-- |
|
| 4329 | 4336 |
-- Name: plantname_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4330 | 4337 |
-- |
| 4331 | 4338 |
|
| ... | ... | |
| 5051 | 5058 |
|
| 5052 | 5059 |
|
| 5053 | 5060 |
-- |
| 5061 |
-- Name: plantconcept_datasource_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5062 |
-- |
|
| 5063 |
|
|
| 5064 |
ALTER TABLE ONLY plantconcept |
|
| 5065 |
ADD CONSTRAINT plantconcept_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5066 |
|
|
| 5067 |
|
|
| 5068 |
-- |
|
| 5054 | 5069 |
-- Name: plantconcept_plantname_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5055 | 5070 |
-- |
| 5056 | 5071 |
|
Also available in: Unified diff
schemas/vegbien.sql: plantconcept: Added datasource_id and appropriate unique indexes to enable scoping by datasource. Moved plantcode right after datasource_id because it will be used for the sourceaccessioncode (if any).