# HG changeset patch # User Raimund Renkert # Date 1467626690 -7200 # Node ID 9f903c54141b34c31e475acf1988ae93a83fcb2a # Parent 6b204c617c1feed003c28f7d806a4c9e905a80c9 Reordered tables. diff -r 6b204c617c1f -r 9f903c54141b db_schema/stammdaten_schema.sql --- a/db_schema/stammdaten_schema.sql Mon Jul 04 11:46:42 2016 +0200 +++ b/db_schema/stammdaten_schema.sql Mon Jul 04 12:04:50 2016 +0200 @@ -85,6 +85,30 @@ $$; +CREATE TABLE koordinaten_art ( + id serial PRIMARY KEY, + koordinatenart character varying(50), + idf_geo_key character varying(1) +); + + +CREATE TABLE mess_einheit ( + id serial PRIMARY KEY, + beschreibung character varying(50), + einheit character varying(12), + eudf_messeinheit_id character varying(8), + umrechnungs_faktor_eudf bigint +); + + +CREATE TABLE umwelt ( + id character varying(3) PRIMARY KEY, + beschreibung character varying(300), + umwelt_bereich character varying(80) NOT NULL, + meh_id integer REFERENCES mess_einheit +); + + CREATE TABLE staat ( id serial PRIMARY KEY, staat character varying(50) NOT NULL, @@ -138,16 +162,6 @@ ); -CREATE TABLE auth ( - id serial PRIMARY KEY, - ldap_group character varying(40) NOT NULL, - netzbetreiber_id character varying(2), - mst_id character varying(5), - labor_mst_id character varying(5), - funktion_id smallint -); - - CREATE TABLE auth_funktion ( id smallint PRIMARY KEY, funktion character varying(40) UNIQUE NOT NULL @@ -159,6 +173,16 @@ INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land'); +CREATE TABLE auth ( + id serial PRIMARY KEY, + ldap_group character varying(40) NOT NULL, + netzbetreiber_id character varying(2) REFERENCES netz_betreiber, + mst_id character varying(5) REFERENCES mess_stelle, + labor_mst_id character varying(5) REFERENCES mess_stelle, + funktion_id smallint REFERENCES auth_funktion +); + + CREATE TABLE auth_lst_umw ( id serial PRIMARY KEY, mst_id character varying(5), @@ -276,22 +300,6 @@ ); -CREATE TABLE koordinaten_art ( - id serial PRIMARY KEY, - koordinatenart character varying(50), - idf_geo_key character varying(1) -); - - -CREATE TABLE mess_einheit ( - id serial PRIMARY KEY, - beschreibung character varying(50), - einheit character varying(12), - eudf_messeinheit_id character varying(8), - umrechnungs_faktor_eudf bigint -); - - CREATE TABLE mess_methode ( id character varying(2) PRIMARY KEY, beschreibung character varying(300), @@ -573,14 +581,6 @@ -- Status workflow -CREATE TABLE umwelt ( - id character varying(3) PRIMARY KEY, - beschreibung character varying(300), - umwelt_bereich character varying(80) NOT NULL, - meh_id integer -); - - ALTER TABLE ONLY lada_user ADD CONSTRAINT lada_user_name_key UNIQUE (name); @@ -607,76 +607,4 @@ CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); - -ALTER TABLE ONLY auth - ADD CONSTRAINT auth_funktion_fkey FOREIGN KEY (funktion_id) REFERENCES auth_funktion(id); - - - -ALTER TABLE ONLY auth - ADD CONSTRAINT auth_labor_fkey FOREIGN KEY (labor_mst_id) REFERENCES mess_stelle(id); - - - -ALTER TABLE ONLY auth_lst_umw - ADD CONSTRAINT auth_lst_umw_lst_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id); - - - -ALTER TABLE ONLY auth_lst_umw - ADD CONSTRAINT auth_lst_umw_umw_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id); - - - -ALTER TABLE ONLY auth - ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id); - - - -ALTER TABLE ONLY auth - ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); - - - -ALTER TABLE ONLY deskriptoren - ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id); - - - -ALTER TABLE ONLY pflicht_messgroesse - ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id); - - - -ALTER TABLE ONLY pflicht_messgroesse - ADD CONSTRAINT pflicht_messgroesse_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES mess_methode(id); - - - -ALTER TABLE ONLY pflicht_messgroesse - ADD CONSTRAINT pflicht_messgroesse_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id); - - - -ALTER TABLE ONLY proben_zusatz - ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); - - - -ALTER TABLE ONLY staat - ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); - - - - - -ALTER TABLE ONLY umwelt - ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); - - - -ALTER TABLE ONLY verwaltungseinheit - ADD CONSTRAINT verwaltungseinheit_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); - - COMMIT;