Mercurial > lada > lada-server
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;