-- Name: referencetype; Type: TYPE; Schema: public; Owner: -

--

CREATE TYPE referencetype AS ENUM (

    'herbarium',

    'primary database',

    'aggregator',

    'book',

    'article',

    'chapter',

    'conference proceedings',

    'dissertation',

    'edited book',

    'generic',

    'journal',

    'manuscript',

    'personal communication',

    'presentation',

    'report',

    'thesis',

    'website'

);

--

-- Name: TYPE referencetype; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON TYPE referencetype IS 'From <https://projects.nceas.ucsb.edu/nceas/attachments/download/428/bien_web_datasource_schema.sql>, <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1300&entity=dba_fielddescription&params=1300>, <http://vegbiendev.nceas.ucsb.edu/phppgadmin/display.php?server=localhost%3A5432%3Aallow&database=vegbien&schema=VegBank&table=reference&subject=table#referencetype>';

--

    reference_id_ integer :=

        (SELECT reference_id FROM reference WHERE shortname = 'NCBI');

                WHERE reference_id = reference_id_

                AND reference_id = reference_id_

-- Name: party_reference_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -

CREATE FUNCTION party_reference_id_self_ref() RETURNS trigger

    IF new.reference_id = 0 THEN -- make self-reference

        new.reference_id = new.party_id;

    IF new.matched_place_id = 0 THEN -- make self-reference

    reference_id integer NOT NULL,

    georeference_valid boolean,

            IF new.matched_place_id = new.place_id THEN -- self-reference

                new.canon_place_id := new.place_id; -- make self-reference

            new.canon_place_id := new.place_id; -- make self-reference

-- Name: reference_by_shortname(text); Type: FUNCTION; Schema: public; Owner: -

CREATE FUNCTION reference_by_shortname(shortname text) RETURNS integer

SELECT reference_id FROM reference WHERE shortname = $1 LIMIT 1

    IF new.matched_label_id = 0 THEN -- make self-reference

    reference_id integer NOT NULL,

-- Name: COLUMN taxonlabel.reference_id; Type: COMMENT; Schema: public; Owner: -

COMMENT ON COLUMN taxonlabel.reference_id IS 'The entity that created the taxon label.';

            IF new.matched_label_id = new.taxonlabel_id THEN -- self-reference

                new.canon_label_id := new.taxonlabel_id; -- make self-reference

            new.canon_label_id := new.taxonlabel_id; -- make self-reference

    reference_id integer NOT NULL,

    "georeferenceValid" boolean,

    "georeferenceValid" boolean,

    SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."georeferenceValid", analytical_stem."isNewWorld", analytical_stem."distanceToCountry_km", analytical_stem."distanceToStateProvince_km", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis", sum(analytical_stem."coverPercent") AS "coverPercent", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision))) AS "individualCount_1cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision))) AS "individualCount_2_5cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_stem GROUP BY analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."georeferenceValid", analytical_stem."isNewWorld", analytical_stem."distanceToCountry_km", analytical_stem."distanceToStateProvince_km", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis";

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

    reference_id integer NOT NULL,

-- Name: COLUMN method.reference_id; Type: COMMENT; Schema: public; Owner: -

COMMENT ON COLUMN method.reference_id IS 'Use the reference table (e.g. reference.url) to store a link to the original plain text description.';

    reference_id integer NOT NULL,

    CONSTRAINT party_required_key CHECK (((organizationname IS NOT NULL) OR ((reference_id <> party_id) AND ((fullname IS NOT NULL) OR (surname IS NOT NULL)))))

    reference_id integer NOT NULL,

-- Name: reference; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE reference (

    reference_id integer NOT NULL,

    referencetype referencetype,

    referencejournal_id integer,

    reference_id integer NOT NULL,

    reference_id integer,

    reference_id integer NOT NULL,

    SELECT reference.shortname AS "institutionCode", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", canon_place.georeference_valid AS "georeferenceValid", "newWorldCountries"."isNewWorld", _m_to_km(canon_place.distance_to_country_m) AS "distanceToCountry_km", _m_to_km(canon_place.distance_to_state_m) AS "distanceToStateProvince_km", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", family_higher_plant_group.higher_plant_group AS "higherPlantGroup", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonlabel.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", COALESCE(taxonoccurrence.iscultivated, (geoscrub_cultivated."isCultivated")::boolean) AS cultivated, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis ELSE (geoscrub_cultivated."isCultivatedReason" || ''::text) END AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.collector_id AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM (((((((((((((((((((((((reference JOIN location USING (reference_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN geoscrub.geoscrub_cultivated ON (((geoscrub_cultivated."latitudeDecimalVerbatim" = coordinates.latitude_deg) AND (geoscrub_cultivated."longitudeDecimalVerbatim" = coordinates.longitude_deg)))) LEFT JOIN "newWorld"."newWorldCountries" ON (("newWorldCountries"."countryNameStd" = canon_place.country))) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) WHERE (NOT taxondetermination.isoriginal);

    reference_id integer,

    reference_id integer,

    reference_id integer,

    reference_id integer,

    reference_id integer NOT NULL,

-- Name: reference_reference_id_seq; Type: SEQUENCE; Schema: public; Owner: -

CREATE SEQUENCE reference_reference_id_seq

-- Name: reference_reference_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

ALTER SEQUENCE reference_reference_id_seq OWNED BY reference.reference_id;

-- Name: reference_reference_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -

-- Name: referencecontributor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE referencecontributor (

    referencecontributor_id integer NOT NULL,

    reference_id integer NOT NULL,

    referenceparty_id integer NOT NULL,

    roletype text,

    "position" integer

-- Name: referencecontributor_referencecontributor_id_seq; Type: SEQUENCE; Schema: public; Owner: -

CREATE SEQUENCE referencecontributor_referencecontributor_id_seq

-- Name: referencecontributor_referencecontributor_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

ALTER SEQUENCE referencecontributor_referencecontributor_id_seq OWNED BY referencecontributor.referencecontributor_id;

-- Name: referencecontributor_referencecontributor_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -

-- Name: referencejournal; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE referencejournal (

    referencejournal_id integer NOT NULL,

    journal text NOT NULL,

    issn text,

    abbreviation text,

-- Name: referencejournal_referencejournal_id_seq; Type: SEQUENCE; Schema: public; Owner: -

CREATE SEQUENCE referencejournal_referencejournal_id_seq

-- Name: referencejournal_referencejournal_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

ALTER SEQUENCE referencejournal_referencejournal_id_seq OWNED BY referencejournal.referencejournal_id;

-- Name: referencejournal_referencejournal_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -

-- Name: referencename; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE referencename (

    referencename_id integer NOT NULL,

    reference_id integer NOT NULL,

    system text,

    name text NOT NULL,

    matched_reference_id integer

);

--

-- Name: referencename_referencename_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

CREATE SEQUENCE referencename_referencename_id_seq

-- Name: referencename_referencename_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

ALTER SEQUENCE referencename_referencename_id_seq OWNED BY referencename.referencename_id;

-- Name: referencename_referencename_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -

-- Name: revision; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE revision (

    revision_id integer NOT NULL,

    tablename text NOT NULL,

    tableattribute text NOT NULL,

    tablerecord integer NOT NULL,

    previousvaluetext text NOT NULL,

    previousvaluetype text NOT NULL,

    previousrevision_id integer,

    revisiondate date NOT NULL

-- Name: revision_revision_id_seq; Type: SEQUENCE; Schema: public; Owner: -

CREATE SEQUENCE revision_revision_id_seq

-- Name: revision_revision_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

ALTER SEQUENCE revision_revision_id_seq OWNED BY revision.revision_id;

-- Name: revision_revision_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -

-- Name: soilobs; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE soilobs (

    soilobs_id integer NOT NULL,

    locationevent_id integer NOT NULL,

    horizon text DEFAULT 'unknown'::text NOT NULL,

    description text,

    depthtop_m double precision,

    depthbottom_m double precision,

    color text,

    texture text,

    organic_fraction double precision,

    sand_fraction double precision,

    silt_fraction double precision,

    clay_fraction double precision,

    coarse_fraction double precision,

    ph double precision,

    acidity_fraction double precision,

    basesaturation_fraction double precision,

    cationexchangecapacity_cmol_kg double precision,

    conductivity double precision,

    carbon_fraction double precision,

    phosphorus_fraction double precision,

    potassium_fraction double precision,

    magnesium_fraction double precision,

    nitrogen_fraction double precision,

    calcium_fraction double precision,

    sodium_fraction double precision

-- Name: soilobs_soilobs_id_seq; Type: SEQUENCE; Schema: public; Owner: -

CREATE SEQUENCE soilobs_soilobs_id_seq

-- Name: soilobs_soilobs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

ALTER SEQUENCE soilobs_soilobs_id_seq OWNED BY soilobs.soilobs_id;

-- Name: soilobs_soilobs_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -

-- Name: soiltaxon; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE soiltaxon (

    soiltaxon_id integer NOT NULL,

    soilcode text,

    soilname text,

    soillevel integer,

    soilparent_id integer,

    soilframework text,

    accessioncode text

-- Name: soiltaxon_soiltaxon_id_seq; Type: SEQUENCE; Schema: public; Owner: -

CREATE SEQUENCE soiltaxon_soiltaxon_id_seq

-- Name: soiltaxon_soiltaxon_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

ALTER SEQUENCE soiltaxon_soiltaxon_id_seq OWNED BY soiltaxon.soiltaxon_id;

-- Name: soiltaxon_soiltaxon_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -

    reference_id integer NOT NULL,

    concept_reference_id integer

-- Name: COLUMN taxonconcept.concept_reference_id; Type: COMMENT; Schema: public; Owner: -

COMMENT ON COLUMN taxonconcept.concept_reference_id IS 'The entity that defined the taxon concept. This is who the taxon concept is according to.

    reference_id integer,

    SELECT DISTINCT accepted_taxonlabel_descendant.descendant_id AS taxonlabel_id FROM ((taxonlabel datasource_taxonlabel JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonlabel_relationship accepted_taxonlabel_descendant ON ((accepted_taxonlabel_descendant.ancestor_id = accepted_taxonlabel.taxonlabel_id))) WHERE (((datasource_taxonlabel.reference_id = reference_by_shortname('IUCN'::text)) AND (datasource_taxonlabel.taxonomicname IS NOT NULL)) AND (accepted_taxonlabel.rank >= 'species'::taxonrank));

-- Name: reference_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE reference ALTER COLUMN reference_id SET DEFAULT nextval('reference_reference_id_seq'::regclass);

-- Name: referencecontributor_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE referencecontributor ALTER COLUMN referencecontributor_id SET DEFAULT nextval('referencecontributor_referencecontributor_id_seq'::regclass);

-- Name: referencejournal_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE referencejournal ALTER COLUMN referencejournal_id SET DEFAULT nextval('referencejournal_referencejournal_id_seq'::regclass);

-- Name: referencename_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE referencename ALTER COLUMN referencename_id SET DEFAULT nextval('referencename_referencename_id_seq'::regclass);

-- Name: revision_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE revision ALTER COLUMN revision_id SET DEFAULT nextval('revision_revision_id_seq'::regclass);

-- Name: soilobs_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE soilobs ALTER COLUMN soilobs_id SET DEFAULT nextval('soilobs_soilobs_id_seq'::regclass);

-- Name: soiltaxon_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE soiltaxon ALTER COLUMN soiltaxon_id SET DEFAULT nextval('soiltaxon_soiltaxon_id_seq'::regclass);

-- Data for Name: reference; Type: TABLE DATA; Schema: public; Owner: -

-- Data for Name: referencecontributor; Type: TABLE DATA; Schema: public; Owner: -

-- Data for Name: referencejournal; Type: TABLE DATA; Schema: public; Owner: -

-- Data for Name: referencename; Type: TABLE DATA; Schema: public; Owner: -

-- Data for Name: revision; Type: TABLE DATA; Schema: public; Owner: -

-- Data for Name: soilobs; Type: TABLE DATA; Schema: public; Owner: -

-- Data for Name: soiltaxon; Type: TABLE DATA; Schema: public; Owner: -

    ADD CONSTRAINT project_unique UNIQUE (reference_id, projectname);

-- Name: reference_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE ONLY reference

    ADD CONSTRAINT reference_pkey PRIMARY KEY (reference_id);

-- Name: referencecontributor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE ONLY referencecontributor

    ADD CONSTRAINT referencecontributor_pkey PRIMARY KEY (referencecontributor_id);

-- Name: referencejournal_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE ONLY referencejournal

    ADD CONSTRAINT referencejournal_pkey PRIMARY KEY (referencejournal_id);

-- Name: referencename_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE ONLY referencename

    ADD CONSTRAINT referencename_pkey PRIMARY KEY (referencename_id);

-- Name: revision_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE ONLY revision

    ADD CONSTRAINT revision_pkey PRIMARY KEY (revision_id);

-- Name: soilobs_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE ONLY soilobs

    ADD CONSTRAINT soilobs_pkey PRIMARY KEY (soilobs_id);

-- Name: soilobs_unique; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE ONLY soilobs

    ADD CONSTRAINT soilobs_unique UNIQUE (locationevent_id);

-- Name: soiltaxon_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

ALTER TABLE ONLY soiltaxon

    ADD CONSTRAINT soiltaxon_pkey PRIMARY KEY (soiltaxon_id);

CREATE UNIQUE INDEX aggregateoccurrence_unique_within_creator ON aggregateoccurrence USING btree (reference_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX coordinates_unique ON coordinates USING btree (reference_id, latitude_deg, longitude_deg, (COALESCE(verbatimlatitude, '\\N'::text)), (COALESCE(verbatimlongitude, '\\N'::text)), (COALESCE(verbatimcoordinates, '\\N'::text)), (COALESCE(footprintgeometry_dwc, '\\N'::text)));

CREATE INDEX location_creator ON location USING btree (reference_id);

CREATE UNIQUE INDEX location_unique_within_creator_by_authorlocationcode ON location USING btree (reference_id, (COALESCE(authorlocationcode, '\\N'::text))) WHERE (((authorlocationcode IS NOT NULL) AND (parent_id IS NULL)) AND (sourceaccessioncode IS NULL));

CREATE UNIQUE INDEX location_unique_within_creator_by_sourceaccessioncode ON location USING btree (reference_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX locationevent_unique_within_creator ON locationevent USING btree (reference_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX method_unique ON method USING btree (reference_id, (COALESCE(name, '\\N'::text)), (COALESCE(description, '\\N'::text)), (COALESCE(observationmeasure, '\\N'::text)));

CREATE UNIQUE INDEX party_unique ON party USING btree (reference_id, (COALESCE(organizationname, '\\N'::text)), (COALESCE(fullname, '\\N'::text)), (COALESCE(surname, '\\N'::text)), (COALESCE(givenname, '\\N'::text)), (COALESCE(middlename, '\\N'::text)), (COALESCE(suffix, '\\N'::text)));

CREATE UNIQUE INDEX place_unique_within_creator_by_code ON place USING btree (reference_id, (COALESCE(placecode, '\\N'::text))) WHERE (placecode IS NOT NULL);

CREATE UNIQUE INDEX place_unique_within_creator_by_name ON place USING btree (reference_id, (COALESCE(continent, '\\N'::text)), (COALESCE(country, '\\N'::text)), (COALESCE(stateprovince, '\\N'::text)), (COALESCE(county, '\\N'::text)), (COALESCE(coordinates_id, 2147483647))) WHERE (placecode IS NULL);

CREATE UNIQUE INDEX plantobservation_unique_within_creator ON plantobservation USING btree (reference_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX project_unique_name_date ON project USING btree (reference_id, (COALESCE(projectname, '\\N'::text)), (COALESCE(startdate, 'infinity'::date))) WHERE (sourceaccessioncode IS NULL);

CREATE UNIQUE INDEX project_unique_within_creator ON project USING btree (reference_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

-- Name: reference_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX reference_accessioncode_index ON reference USING btree (accessioncode);

-- Name: reference_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX reference_unique ON reference USING btree ((COALESCE(shortname, '\\N'::text)));

-- Name: referencejournal_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX referencejournal_accessioncode_index ON referencejournal USING btree (accessioncode);

-- Name: referencename_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX referencename_unique ON referencename USING btree (reference_id, (COALESCE(name, '\\N'::text)));

-- Name: soiltaxon_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE UNIQUE INDEX soiltaxon_accessioncode_index ON soiltaxon USING btree (accessioncode);

CREATE UNIQUE INDEX specimenreplicate_unique_catalognumber ON specimenreplicate USING btree (reference_id, (COALESCE(institution_id, 2147483647)), (COALESCE(collectioncode_dwc, '\\N'::text)), (COALESCE(catalognumber_dwc, '\\N'::text)), (COALESCE(plantobservation_id, 2147483647))) WHERE ((catalognumber_dwc IS NOT NULL) AND (sourceaccessioncode IS NULL));

CREATE UNIQUE INDEX specimenreplicate_unique_within_creator ON specimenreplicate USING btree (reference_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX stemobservation_unique_within_creator ON stemobservation USING btree (reference_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX taxonlabel_0_unique_identifying_name ON taxonlabel USING btree (reference_id, (COALESCE(taxonomicname, '\\N'::text))) WHERE (taxonomicname IS NOT NULL);

CREATE UNIQUE INDEX taxonlabel_1_unique_sourceaccessioncode ON taxonlabel USING btree (reference_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

CREATE UNIQUE INDEX taxonlabel_unique ON taxonlabel USING btree ((COALESCE(parent_id, 2147483647)), (COALESCE(taxonepithet, '\\N'::text)), (COALESCE(rank, 'unknown'::taxonrank)), reference_id, (COALESCE(sourceaccessioncode, '\\N'::text)), (COALESCE(taxonomicname, '\\N'::text)));

CREATE UNIQUE INDEX taxonoccurrence_unique_within_creator ON taxonoccurrence USING btree (reference_id, (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE (sourceaccessioncode IS NOT NULL);

-- Name: party_reference_id_self_ref; Type: TRIGGER; Schema: public; Owner: -

CREATE TRIGGER party_reference_id_self_ref BEFORE INSERT OR UPDATE ON party FOR EACH ROW EXECUTE PROCEDURE party_reference_id_self_ref();

-- Name: aggregateoccurrence_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT aggregateoccurrence_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

    ADD CONSTRAINT commclass_classpublication_id_fkey FOREIGN KEY (classpublication_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: commconcept_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT commconcept_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

    ADD CONSTRAINT commdetermination_commauthority_id_fkey FOREIGN KEY (commauthority_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: commname_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT commname_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: commstatus_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT commstatus_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: coordinates_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT coordinates_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: covermethod_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT covermethod_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: location_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT location_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: locationevent_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT locationevent_reference_id_fkey FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: locationevent_soiltaxon_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT locationevent_soiltaxon_id_fkey FOREIGN KEY (soiltaxon_id) REFERENCES soiltaxon(soiltaxon_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: method_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -