changeset 1000:1489f0ade850 schema-update

Moved FK constraints into table definition.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 01 Jul 2016 19:01:32 +0200
parents 813461e9a7be
children 1c4aa7ea63aa
files db_schema/lada_schema.sql
diffstat 1 files changed, 18 insertions(+), 144 deletions(-) [+]
line wrap: on
line diff
--- a/db_schema/lada_schema.sql	Fri Jul 01 18:17:23 2016 +0200
+++ b/db_schema/lada_schema.sql	Fri Jul 01 19:01:32 2016 +0200
@@ -209,8 +209,8 @@
 
 CREATE TABLE messprogramm_mmt (
     id serial PRIMARY KEY,
-    messprogramm_id integer NOT NULL,
-    mmt_id character varying(2) NOT NULL,
+    messprogramm_id integer NOT NULL REFERENCES messprogramm ON DELETE CASCADE,
+    mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode,
     messgroessen integer[],
     letzte_aenderung timestamp without time zone DEFAULT now()
 );
@@ -263,7 +263,7 @@
     erzeuger character varying(5) NOT NULL,
     datum timestamp without time zone DEFAULT now(),
     text character varying(1024),
-    probe_id integer NOT NULL
+    probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE
 );
 
 
@@ -273,9 +273,9 @@
 
 CREATE TABLE ortszuordnung (
     id serial PRIMARY KEY,
-    probe_id integer NOT NULL,
-    ort_id bigint NOT NULL,
-    ortszuordnung_typ character varying(1),
+    probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
+    ort_id bigint NOT NULL REFERENCES stammdaten.ort,
+    ortszuordnung_typ character varying(1) REFERENCES stammdaten.ortszuordnung_typ,
     ortszusatztext character varying(100),
     letzte_aenderung timestamp without time zone DEFAULT now(),
     tree_modified timestamp without time zone DEFAULT now()
@@ -289,8 +289,8 @@
 
 CREATE TABLE zusatz_wert (
     id serial PRIMARY KEY,
-    probe_id integer NOT NULL,
-    pzs_id character varying(3) NOT NULL,
+    probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
+    pzs_id character varying(3) NOT NULL REFERENCES stammdaten.proben_zusatz,
     messwert_pzs double precision,
     messfehler real,
     letzte_aenderung timestamp without time zone DEFAULT now(),
@@ -385,13 +385,13 @@
 CREATE TABLE messung (
     id serial PRIMARY KEY,
     id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL,
-    probe_id integer NOT NULL,
+    probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
     nebenproben_nr character varying(10),
-    mmt_id character varying(2) NOT NULL,
+    mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode ON DELETE CASCADE,
     messdauer integer,
     messzeitpunkt timestamp with time zone,
     fertig boolean DEFAULT false NOT NULL,
-    status integer,
+    status integer REFERENCES status_protokoll,
     letzte_aenderung timestamp without time zone DEFAULT now(),
     geplant boolean DEFAULT false NOT NULL,
     tree_modified timestamp without time zone DEFAULT now()
@@ -407,7 +407,7 @@
     erzeuger character varying(5) NOT NULL,
     datum timestamp without time zone DEFAULT now(),
     text character varying(1024),
-    messungs_id integer NOT NULL
+    messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE
 );
 
 
@@ -417,13 +417,13 @@
 
 CREATE TABLE messwert (
     id serial PRIMARY KEY,
-    messungs_id integer NOT NULL,
-    messgroesse_id integer NOT NULL,
+    messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
+    messgroesse_id integer NOT NULL REFERENCES stammdaten.messgroesse,
     messwert_nwg character varying(1),
     messwert double precision NOT NULL,
     messfehler real,
     nwg_zu_messwert double precision,
-    meh_id smallint NOT NULL,
+    meh_id smallint NOT NULL REFERENCES stammdaten.mess_einheit,
     grenzwertueberschreitung boolean DEFAULT false,
     letzte_aenderung timestamp without time zone DEFAULT now(),
     tree_modified timestamp without time zone DEFAULT now(),
@@ -441,9 +441,9 @@
     erzeuger character varying(5) NOT NULL,
     datum timestamp without time zone DEFAULT now(),
     text character varying(1024),
-    messungs_id integer NOT NULL,
-    status_stufe integer NOT NULL,
-    status_wert integer NOT NULL,
+    messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
+    status_stufe integer NOT NULL REFERENCES stammdaten.status_stufe,
+    status_wert integer NOT NULL REFERENCES stammdaten.status_wert,
     tree_modified timestamp without time zone DEFAULT now()
 );
 
@@ -511,148 +511,4 @@
 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert();
 
 
---
--- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY status_protokoll
-    ADD CONSTRAINT status_protokoll_status_stufe_fkey FOREIGN KEY (status_stufe) REFERENCES stammdaten.status_stufe(id);
-
-
---
--- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY status_protokoll
-    ADD CONSTRAINT status_protokoll_status_wert_fkey FOREIGN KEY (status_wert) REFERENCES stammdaten.status_wert(id);
-
-
---
--- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY kommentar_m
-    ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
-
-
---
--- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY kommentar_p
-    ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
-
-
---
--- Name: messprogramm_mmt_messprogramm_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY messprogramm_mmt
-    ADD CONSTRAINT messprogramm_mmt_messprogramm_id_fkey FOREIGN KEY (messprogramm_id) REFERENCES messprogramm(id) ON DELETE CASCADE;
-
-
---
--- Name: messprogramm_mmt_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY messprogramm_mmt
-    ADD CONSTRAINT messprogramm_mmt_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id);
-
-
---
--- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY messung
-    ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id) ON DELETE CASCADE;
-
-
---
--- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY messung
-    ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
-
-
---
--- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY messung
-    ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
-
-
---
--- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY messwert
-    ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
-
-
---
--- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY messwert
-    ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id);
-
-
---
--- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY messwert
-    ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
-
-
---
--- Name: ortszuordnung_ort_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY ortszuordnung
-    ADD CONSTRAINT ortszuordnung_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
-
-
---
--- Name: ortszuordnung_ortszuordnung_typ_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY ortszuordnung
-    ADD CONSTRAINT ortszuordnung_ortszuordnung_typ_fkey FOREIGN KEY (ortszuordnung_typ) REFERENCES stammdaten.ortszuordnung_typ(id);
-
-
---
--- Name: ortszuordnung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY ortszuordnung
-    ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
-
-
---
--- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY status_protokoll
-    ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
-
-
---
--- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY zusatz_wert
-    ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
-
-
---
--- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY zusatz_wert
-    ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id);
-
-
 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)