-- Name: _set_canon_taxonconcept(integer, integer); Type: FUNCTION; Schema: public; Owner: -

CREATE FUNCTION _set_canon_taxonconcept(taxonconcept_id integer, canon_taxonconcept_id integer) RETURNS integer

UPDATE taxonconcept SET canon_taxonconcept_id = $2 WHERE taxonconcept_id = $1

RETURNING taxonconcept_id

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

CREATE FUNCTION taxonconcept_canon_taxonconcept_id_self_ref() RETURNS trigger

    IF new.taxonconcept_id IS NULL THEN -- prepopulate taxonconcept_id

        new.taxonconcept_id = nextval('taxonconcept_taxonconcept_id_seq'::regclass);

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

        new.canon_taxonconcept_id = new.taxonconcept_id;

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

CREATE TABLE taxonconcept (

    taxonconcept_id integer NOT NULL,

    datasource_id integer NOT NULL,

    identifyingtaxonomicname text,

    canon_taxonconcept_id integer,

    taxon_id integer,

    taxonomicname text,

    author text,

    taxonomicnamewithauthor text,

    domain text,

    kingdom text,

    phylum text,

    class text,

    "order" text,

    family text,

    genus text,

    species text,

    subspecies text,

    variety text,

    forma text,

    cultivar text,

    morphospecies text,

    otherranks rankedtaxonname[],

    scope_id integer,

    accessioncode text,

    CONSTRAINT taxonconcept_required_key CHECK (((((((identifyingtaxonomicname IS NOT NULL) OR (taxonomicname IS NOT NULL)) OR (taxonomicnamewithauthor IS NOT NULL)) OR (family IS NOT NULL)) OR (genus IS NOT NULL)) OR (otherranks IS NOT NULL)))

-- Name: TABLE taxonconcept; Type: COMMENT; Schema: public; Owner: -

COMMENT ON TABLE taxonconcept IS 'The full path to a taxon, including all components of the taxonomic name. Can be either verbatim or accepted. For accepted names, points to the identified taxon in the tree of life.

To include a taxon name at a rank with no explicit column, add it to the otherranks array.';

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

COMMENT ON COLUMN taxonconcept.identifyingtaxonomicname IS 'The taxonomic name that uniquely identifies this taxonconcept. If set, the other fields will not be used in duplicate elimination.';

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

COMMENT ON COLUMN taxonconcept.canon_taxonconcept_id IS 'The taxonconcept containing the accepted name of this verbatim taxonomic name. taxonconcepts should be linked in a three-level hierarchy of datasource name -> concatenated name -> accepted name.

An accepted name should point to itself in this field. This will happen automatically by setting it to the special value 0.';

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

COMMENT ON COLUMN taxonconcept.taxon_id IS 'The corresponding taxon in the tree of life. Only used for parsed names.';

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

COMMENT ON COLUMN taxonconcept.taxonomicname IS 'The concatenated taxonomic name which uniquely identifies this taxon. Does not include the author of the name.

Equivalent to Darwin Core''s scientificName.';

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

COMMENT ON COLUMN taxonconcept.author IS 'The author of the taxonomic name.

Equivalent to Darwin Core''s scientificNameAuthorship.';

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

COMMENT ON COLUMN taxonconcept.taxonomicnamewithauthor IS 'The concatenated taxonomic name which uniquely identifies this taxon, plus the author of that name.

Equivalent to Darwin Core''s scientificName + scientificNameAuthorship.

Equivalent to "Name sec. x".';

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

COMMENT ON COLUMN taxonconcept.domain IS 'The domain portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.kingdom IS 'The kingdom portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.phylum IS 'The phylum portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.class IS 'The class portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept."order" IS 'The order portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.family IS 'The family portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.genus IS 'The genus portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.species IS 'The species portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.subspecies IS 'The subspecies portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.variety IS 'The variety portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.forma IS 'The forma portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.cultivar IS 'The cultivar portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.morphospecies IS 'The morphospecies portion of the taxonomic name.';

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

COMMENT ON COLUMN taxonconcept.otherranks IS 'Additional ranks which do not have a named column. Put ranks in path order, so that lower-level taxa come after higher-level taxa.';

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

COMMENT ON COLUMN taxonconcept.scope_id IS 'The morphospecies scope.';

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

CREATE TABLE taxondetermination (

    taxondetermination_id integer NOT NULL,

    taxonoccurrence_id integer NOT NULL,

    taxonconcept_id integer NOT NULL,

    party_id integer,

    role role DEFAULT 'unknown'::role NOT NULL,

    determinationtype text,

    reference_id integer,

    isoriginal boolean DEFAULT false NOT NULL,

    iscurrent boolean DEFAULT false NOT NULL,

    taxonfit text,

    taxonconfidence text,

    grouptype text,

    notes text,

    notespublic boolean,

    notesmgt boolean,

    revisions boolean,

    determinationdate timestamp with time zone,

    accessioncode text

);

-- Name: TABLE taxondetermination; Type: COMMENT; Schema: public; Owner: -

COMMENT ON TABLE taxondetermination IS 'Equivalent to VegBank''s taxoninterpretation table.';

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

CREATE TABLE taxonoccurrence (

    taxonoccurrence_id integer NOT NULL,

    datasource_id integer NOT NULL,

    sourceaccessioncode text,

    locationevent_id integer,

    authortaxoncode text,

    verbatimcollectorname text,

    growthform growthform,

    iscultivated boolean,

    cultivatedbasis text,

    isnative boolean,

    accessioncode text

);

-- Name: TABLE taxonoccurrence; Type: COMMENT; Schema: public; Owner: -

COMMENT ON TABLE taxonoccurrence IS 'Equivalent to VegBank''s taxonobservation table.';

-- Name: COLUMN taxonoccurrence.iscultivated; Type: COMMENT; Schema: public; Owner: -

COMMENT ON COLUMN taxonoccurrence.iscultivated IS 'cultivated or wild';

-- Name: COLUMN taxonoccurrence.cultivatedbasis; Type: COMMENT; Schema: public; Owner: -

COMMENT ON COLUMN taxonoccurrence.cultivatedbasis IS 'The reason why a taxonoccurrence was marked as cultivated (or not).';

-- Name: COLUMN taxonoccurrence.isnative; Type: COMMENT; Schema: public; Owner: -

COMMENT ON COLUMN taxonoccurrence.isnative IS 'native or exotic';

    SELECT datasource.organizationname AS "dataSourceName", taxonconcept.family, taxonconcept.genus, taxonconcept.species, COALESCE(taxonconcept.taxonomicnamewithauthor, taxonconcept.taxonomicname) AS taxon, taxonconcept.author AS "taxonAuthor", taxonconcept.variety AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", functions._fraction_to_percent(aggregateoccurrence.cover_fraction) AS "pctCover" FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) 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 taxonconcept USING (taxonconcept_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

    taxonconcept_id integer,

    CONSTRAINT methodtaxonclass_key_required CHECK (((taxonconcept_id IS NOT NULL) OR (taxonclass IS NOT NULL)))

    taxonconcept_id integer NOT NULL,

    taxonconcept_id integer NOT NULL,

    taxonconceptstatus text DEFAULT 'undetermined'::text NOT NULL,

    taxonconcept_id integer,

    taxonconcept_id integer NOT NULL,

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

CREATE SEQUENCE taxonconcept_taxonconcept_id_seq

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

ALTER SEQUENCE taxonconcept_taxonconcept_id_seq OWNED BY taxonconcept.taxonconcept_id;

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

CREATE SEQUENCE taxondetermination_taxondetermination_id_seq

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

ALTER SEQUENCE taxondetermination_taxondetermination_id_seq OWNED BY taxondetermination.taxondetermination_id;

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

CREATE SEQUENCE taxonoccurrence_taxonoccurrence_id_seq

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

ALTER SEQUENCE taxonoccurrence_taxonoccurrence_id_seq OWNED BY taxonoccurrence.taxonoccurrence_id;

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

ALTER TABLE taxonconcept ALTER COLUMN taxonconcept_id SET DEFAULT nextval('taxonconcept_taxonconcept_id_seq'::regclass);

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

ALTER TABLE taxondetermination ALTER COLUMN taxondetermination_id SET DEFAULT nextval('taxondetermination_taxondetermination_id_seq'::regclass);

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

ALTER TABLE taxonoccurrence ALTER COLUMN taxonoccurrence_id SET DEFAULT nextval('taxonoccurrence_taxonoccurrence_id_seq'::regclass);

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

    ADD CONSTRAINT methodtaxonclass_unique_taxonconcept_id UNIQUE (method_id, taxonconcept_id);

    ADD CONSTRAINT plantstatus_unique UNIQUE (taxonconcept_id, party_id);

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

--

ALTER TABLE ONLY taxonconcept

    ADD CONSTRAINT taxonconcept_pkey PRIMARY KEY (taxonconcept_id);

--

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

CREATE UNIQUE INDEX taxonconcept_accessioncode_index ON taxonconcept USING btree (accessioncode);

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

CREATE UNIQUE INDEX taxonconcept_unique_within_datasource_by_identifying_name ON taxonconcept USING btree (datasource_id, (COALESCE(identifyingtaxonomicname, '\\N'::text))) WHERE (identifyingtaxonomicname IS NOT NULL);

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

CREATE UNIQUE INDEX taxonconcept_unique_within_datasource_by_name ON taxonconcept USING btree (datasource_id, (COALESCE(taxonomicname, '\\N'::text)), (COALESCE(author, '\\N'::text)), (COALESCE(taxonomicnamewithauthor, '\\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)), (COALESCE(morphospecies, '\\N'::text)), (COALESCE(otherranks, ARRAY[]::rankedtaxonname[])), (COALESCE(scope_id, 2147483647))) WHERE (identifyingtaxonomicname IS NULL);

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

CREATE UNIQUE INDEX taxondetermination_accessioncode_index ON taxondetermination USING btree (accessioncode);

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

CREATE UNIQUE INDEX taxondetermination_unique ON taxondetermination USING btree (taxonoccurrence_id, isoriginal, role, (COALESCE(party_id, 2147483647)), taxonconcept_id);

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

CREATE UNIQUE INDEX taxonoccurrence_accessioncode_index ON taxonoccurrence USING btree (accessioncode);

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

CREATE INDEX taxonoccurrence_locationevent ON taxonoccurrence USING btree (locationevent_id);

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

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

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

CREATE UNIQUE INDEX taxonoccurrence_unique_within_locationevent ON taxonoccurrence USING btree ((COALESCE(locationevent_id, 2147483647)), (COALESCE(authortaxoncode, '\\N'::text))) WHERE (sourceaccessioncode IS NULL);

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

CREATE TRIGGER taxonconcept_canon_taxonconcept_id_self_ref BEFORE INSERT OR UPDATE ON taxonconcept FOR EACH ROW EXECUTE PROCEDURE taxonconcept_canon_taxonconcept_id_self_ref();

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

    ADD CONSTRAINT methodtaxonclass_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT plantcorrelation_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT plantstatus_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT plantusage_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT taxonalt_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

    ADD CONSTRAINT taxonalt_taxondetermination_id_fkey FOREIGN KEY (taxondetermination_id) REFERENCES taxondetermination(taxondetermination_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY taxonconcept

    ADD CONSTRAINT taxonconcept_canon_taxonconcept_id_fkey FOREIGN KEY (canon_taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY taxonconcept

    ADD CONSTRAINT taxonconcept_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY taxonconcept

    ADD CONSTRAINT taxonconcept_scope_id_fkey FOREIGN KEY (scope_id) REFERENCES taxonscope(taxonscope_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY taxonconcept

    ADD CONSTRAINT taxonconcept_taxon_id_fkey FOREIGN KEY (taxon_id) REFERENCES taxon(taxon_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY taxondetermination

    ADD CONSTRAINT taxondetermination_party_id_fkey FOREIGN KEY (party_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY taxondetermination

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

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

ALTER TABLE ONLY taxondetermination

    ADD CONSTRAINT taxondetermination_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY taxondetermination

    ADD CONSTRAINT taxondetermination_taxonoccurrence_id_fkey FOREIGN KEY (taxonoccurrence_id) REFERENCES taxonoccurrence(taxonoccurrence_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY taxonoccurrence

    ADD CONSTRAINT taxonoccurrence_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

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

ALTER TABLE ONLY taxonoccurrence

    ADD CONSTRAINT taxonoccurrence_locationevent_id_fkey FOREIGN KEY (locationevent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE;