Mercurial > lada > lada-server
comparison db_schema/stammdaten_schema.sql @ 902:d465094946ef
Improve readability of DDL for editable stammdaten tables.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 24 Mar 2016 15:57:05 +0100 |
parents | d4d768295068 |
children | 825d82b8ad6e |
comparison
equal
deleted
inserted
replaced
901:56c9fb1d37c8 | 902:d465094946ef |
---|---|
75 return (result); | 75 return (result); |
76 end; | 76 end; |
77 $$; | 77 $$; |
78 | 78 |
79 | 79 |
80 CREATE SEQUENCE staat_id_seq | |
81 START WITH 1 | |
82 INCREMENT BY 1 | |
83 NO MINVALUE | |
84 NO MAXVALUE | |
85 CACHE 1; | |
86 | |
87 CREATE TABLE staat ( | |
88 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass), | |
89 staat character varying(50) NOT NULL, | |
90 hkl_id smallint NOT NULL, | |
91 staat_iso character varying(2) NOT NULL, | |
92 staat_kurz character varying(5), | |
93 eu character(1) DEFAULT NULL::bpchar, | |
94 koord_x_extern character varying(22), | |
95 koord_y_extern character varying(22), | |
96 kda_id integer | |
97 ); | |
98 | |
99 ALTER SEQUENCE staat_id_seq OWNED BY staat.id; | |
100 | |
101 | |
102 CREATE TABLE verwaltungseinheit ( | |
103 id character varying(8) PRIMARY KEY, | |
104 bundesland character varying(8) NOT NULL, | |
105 kda_id integer, | |
106 kreis character varying(8), | |
107 nuts character varying(10), | |
108 regbezirk character varying(8), | |
109 bezeichnung character varying(80) NOT NULL, | |
110 is_bundesland character(1) NOT NULL, | |
111 is_gemeinde character(1) NOT NULL, | |
112 is_landkreis character(1) NOT NULL, | |
113 is_regbezirk character(1) NOT NULL, | |
114 koord_x_extern character varying(22), | |
115 koord_y_extern character varying(22), | |
116 plz character varying(6), | |
117 longitude double precision, | |
118 latitude double precision | |
119 ); | |
120 | |
121 | |
122 CREATE TABLE netz_betreiber ( | |
123 id character varying(2) PRIMARY KEY, | |
124 netzbetreiber character varying(50), | |
125 idf_netzbetreiber character varying(1), | |
126 is_bmn boolean DEFAULT false, | |
127 mailverteiler character varying(512), | |
128 aktiv boolean DEFAULT false, | |
129 zust_mst_id character varying(5) | |
130 ); | |
131 | |
132 | |
133 CREATE TABLE mess_stelle ( | |
134 id character varying(5) PRIMARY KEY, | |
135 netzbetreiber_id character varying(2), | |
136 beschreibung character varying(300), | |
137 mess_stelle character varying(60), | |
138 mst_typ character varying(1), | |
139 amtskennung character varying(6) | |
140 ); | |
141 | |
142 | |
80 CREATE SEQUENCE auth_id_seq | 143 CREATE SEQUENCE auth_id_seq |
81 START WITH 1 | 144 START WITH 1 |
82 INCREMENT BY 1 | 145 INCREMENT BY 1 |
83 NO MINVALUE | 146 NO MINVALUE |
84 NO MAXVALUE | 147 NO MAXVALUE |
147 CACHE 1; | 210 CACHE 1; |
148 | 211 |
149 CREATE TABLE datensatz_erzeuger ( | 212 CREATE TABLE datensatz_erzeuger ( |
150 id integer PRIMARY KEY | 213 id integer PRIMARY KEY |
151 DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass), | 214 DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass), |
152 netzbetreiber_id character varying(2), | 215 netzbetreiber_id character varying(2) REFERENCES netz_betreiber, |
153 da_erzeuger_id character varying(2), | 216 da_erzeuger_id character varying(2), |
154 mst_id character varying(5), | 217 mst_id character varying(5) REFERENCES mess_stelle, |
155 bezeichnung character varying(120), | 218 bezeichnung character varying(120), |
156 letzte_aenderung timestamp without time zone | 219 letzte_aenderung timestamp without time zone |
157 ); | 220 ); |
158 | 221 |
159 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id; | 222 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id; |
334 beschreibung character varying(300), | 397 beschreibung character varying(300), |
335 messmethode character varying(50) | 398 messmethode character varying(50) |
336 ); | 399 ); |
337 | 400 |
338 | 401 |
339 CREATE TABLE mess_stelle ( | |
340 id character varying(5) PRIMARY KEY, | |
341 netzbetreiber_id character varying(2), | |
342 beschreibung character varying(300), | |
343 mess_stelle character varying(60), | |
344 mst_typ character varying(1), | |
345 amtskennung character varying(6) | |
346 ); | |
347 | |
348 | |
349 | |
350 CREATE SEQUENCE messgroesse_id_seq | 402 CREATE SEQUENCE messgroesse_id_seq |
351 START WITH 1 | 403 START WITH 1 |
352 INCREMENT BY 1 | 404 INCREMENT BY 1 |
353 NO MINVALUE | 405 NO MINVALUE |
354 NO MAXVALUE | 406 NO MAXVALUE |
393 CACHE 1; | 445 CACHE 1; |
394 | 446 |
395 CREATE TABLE messprogramm_kategorie ( | 447 CREATE TABLE messprogramm_kategorie ( |
396 id integer PRIMARY KEY | 448 id integer PRIMARY KEY |
397 DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass), | 449 DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass), |
398 netzbetreiber_id character varying(2), | 450 netzbetreiber_id character varying(2) REFERENCES netz_betreiber, |
399 mpl_id character varying(3), | 451 mpl_id character varying(3), |
400 bezeichnung character varying(120), | 452 bezeichnung character varying(120), |
401 letzte_aenderung timestamp without time zone | 453 letzte_aenderung timestamp without time zone |
402 ); | 454 ); |
403 | 455 |
423 FROM mmt_messgroesse_grp, | 475 FROM mmt_messgroesse_grp, |
424 mg_grp | 476 mg_grp |
425 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id); | 477 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id); |
426 | 478 |
427 | 479 |
428 CREATE TABLE netz_betreiber ( | 480 CREATE TABLE ort_typ ( |
429 id character varying(2) PRIMARY KEY, | 481 id smallint PRIMARY KEY, |
430 netzbetreiber character varying(50), | 482 ort_typ character varying(60) |
431 idf_netzbetreiber character varying(1), | |
432 is_bmn boolean DEFAULT false, | |
433 mailverteiler character varying(512), | |
434 aktiv boolean DEFAULT false, | |
435 zust_mst_id character varying(5) | |
436 ); | 483 ); |
437 | 484 |
438 | 485 |
439 CREATE SEQUENCE ort_id_seq | 486 CREATE SEQUENCE ort_id_seq |
440 START WITH 1 | 487 START WITH 1 |
443 NO MAXVALUE | 490 NO MAXVALUE |
444 CACHE 1; | 491 CACHE 1; |
445 | 492 |
446 CREATE TABLE ort ( | 493 CREATE TABLE ort ( |
447 id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass), | 494 id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass), |
448 netzbetreiber_id character varying(2), | 495 netzbetreiber_id character varying(2) REFERENCES netz_betreiber, |
449 ort_id character varying(10), | 496 ort_id character varying(10), |
450 langtext character varying(100), | 497 langtext character varying(100), |
451 staat_id smallint, | 498 staat_id smallint REFERENCES staat, |
452 gem_id character varying(8), | 499 gem_id character varying(8) REFERENCES verwaltungseinheit, |
453 unscharf character(1) DEFAULT NULL::bpchar, | 500 unscharf character(1) DEFAULT NULL::bpchar, |
454 nuts_code character varying(10), | 501 nuts_code character varying(10), |
455 kda_id integer, | 502 kda_id integer REFERENCES koordinaten_art, |
456 koord_x_extern character varying(22), | 503 koord_x_extern character varying(22), |
457 koord_y_extern character varying(22), | 504 koord_y_extern character varying(22), |
458 hoehe_land real, | 505 hoehe_land real, |
459 letzte_aenderung timestamp without time zone DEFAULT now(), | 506 letzte_aenderung timestamp without time zone DEFAULT now(), |
460 latitude double precision, | 507 latitude double precision, |
461 longitude double precision, | 508 longitude double precision, |
462 geom public.geometry(Point,4326), | 509 geom public.geometry(Point,4326), |
463 shape public.geometry(MultiPolygon,4326), | 510 shape public.geometry(MultiPolygon,4326), |
464 ort_typ smallint, | 511 ort_typ smallint REFERENCES ort_typ, |
465 kurztext character varying(15), | 512 kurztext character varying(15), |
466 berichtstext character varying(70), | 513 berichtstext character varying(70), |
467 zone character varying(1), | 514 zone character varying(1), |
468 sektor character varying(2), | 515 sektor character varying(2), |
469 zustaendigkeit character varying(10), | 516 zustaendigkeit character varying(10), |
471 aktiv character(1), | 518 aktiv character(1), |
472 anlage_id integer, | 519 anlage_id integer, |
473 oz_id integer | 520 oz_id integer |
474 ); | 521 ); |
475 | 522 |
523 ALTER TABLE ONLY ort | |
524 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id); | |
525 | |
526 ALTER TABLE ONLY ort | |
527 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id); | |
528 | |
476 ALTER SEQUENCE ort_id_seq OWNED BY ort.id; | 529 ALTER SEQUENCE ort_id_seq OWNED BY ort.id; |
477 | |
478 | |
479 CREATE TABLE ort_typ ( | |
480 id smallint PRIMARY KEY, | |
481 ort_typ character varying(60) | |
482 ); | |
483 | 530 |
484 | 531 |
485 CREATE TABLE ortszuordnung_typ ( | 532 CREATE TABLE ortszuordnung_typ ( |
486 id character(1) PRIMARY KEY, | 533 id character(1) PRIMARY KEY, |
487 ortstyp character varying(60) | 534 ortstyp character varying(60) |
540 NO MAXVALUE | 587 NO MAXVALUE |
541 CACHE 1; | 588 CACHE 1; |
542 | 589 |
543 CREATE TABLE probenehmer ( | 590 CREATE TABLE probenehmer ( |
544 id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass), | 591 id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass), |
545 netzbetreiber_id character varying(2), | 592 netzbetreiber_id character varying(2) REFERENCES netz_betreiber, |
546 prn_id character varying(9), | 593 prn_id character varying(9), |
547 bearbeiter character varying(25), | 594 bearbeiter character varying(25), |
548 bemerkung character varying(60), | 595 bemerkung character varying(60), |
549 betrieb character varying(80), | 596 betrieb character varying(80), |
550 bezeichnung character varying(80), | 597 bezeichnung character varying(80), |
596 index integer | 643 index integer |
597 ); | 644 ); |
598 | 645 |
599 ALTER SEQUENCE result_id_seq OWNED BY result.id; | 646 ALTER SEQUENCE result_id_seq OWNED BY result.id; |
600 | 647 |
601 | |
602 | |
603 CREATE SEQUENCE staat_id_seq | |
604 START WITH 1 | |
605 INCREMENT BY 1 | |
606 NO MINVALUE | |
607 NO MAXVALUE | |
608 CACHE 1; | |
609 | |
610 CREATE TABLE staat ( | |
611 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass), | |
612 staat character varying(50) NOT NULL, | |
613 hkl_id smallint NOT NULL, | |
614 staat_iso character varying(2) NOT NULL, | |
615 staat_kurz character varying(5), | |
616 eu character(1) DEFAULT NULL::bpchar, | |
617 koord_x_extern character varying(22), | |
618 koord_y_extern character varying(22), | |
619 kda_id integer | |
620 ); | |
621 | |
622 ALTER SEQUENCE staat_id_seq OWNED BY staat.id; | |
623 | 648 |
624 | 649 |
625 -- Status workflow | 650 -- Status workflow |
626 CREATE TABLE status_stufe ( | 651 CREATE TABLE status_stufe ( |
627 id integer PRIMARY KEY, | 652 id integer PRIMARY KEY, |
743 umwelt_bereich character varying(80) NOT NULL, | 768 umwelt_bereich character varying(80) NOT NULL, |
744 meh_id integer | 769 meh_id integer |
745 ); | 770 ); |
746 | 771 |
747 | 772 |
748 CREATE TABLE verwaltungseinheit ( | |
749 id character varying(8) PRIMARY KEY, | |
750 bundesland character varying(8) NOT NULL, | |
751 kda_id integer, | |
752 kreis character varying(8), | |
753 nuts character varying(10), | |
754 regbezirk character varying(8), | |
755 bezeichnung character varying(80) NOT NULL, | |
756 is_bundesland character(1) NOT NULL, | |
757 is_gemeinde character(1) NOT NULL, | |
758 is_landkreis character(1) NOT NULL, | |
759 is_regbezirk character(1) NOT NULL, | |
760 koord_x_extern character varying(22), | |
761 koord_y_extern character varying(22), | |
762 plz character varying(6), | |
763 longitude double precision, | |
764 latitude double precision | |
765 ); | |
766 | |
767 | |
768 | 773 |
769 | 774 |
770 ALTER TABLE ONLY lada_user | 775 ALTER TABLE ONLY lada_user |
771 ADD CONSTRAINT lada_user_name_key UNIQUE (name); | 776 ADD CONSTRAINT lada_user_name_key UNIQUE (name); |
772 | 777 |
822 ALTER TABLE ONLY auth | 827 ALTER TABLE ONLY auth |
823 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); | 828 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); |
824 | 829 |
825 | 830 |
826 | 831 |
827 ALTER TABLE ONLY datensatz_erzeuger | |
828 ADD CONSTRAINT datensatz_erzeuger_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES mess_stelle(id); | |
829 | |
830 | |
831 | |
832 ALTER TABLE ONLY datensatz_erzeuger | |
833 ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); | |
834 | |
835 | |
836 | |
837 ALTER TABLE ONLY favorite | 832 ALTER TABLE ONLY favorite |
838 ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); | 833 ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); |
839 | 834 |
840 | 835 |
841 | 836 |
862 ALTER TABLE ONLY deskriptoren | 857 ALTER TABLE ONLY deskriptoren |
863 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id); | 858 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id); |
864 | 859 |
865 | 860 |
866 | 861 |
867 ALTER TABLE ONLY messprogramm_kategorie | |
868 ADD CONSTRAINT messprogramm_kategorie_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); | |
869 | |
870 | |
871 | |
872 ALTER TABLE ONLY ort | |
873 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id); | |
874 | |
875 | |
876 | |
877 ALTER TABLE ONLY ort | |
878 ADD CONSTRAINT ort_gem_id_fkey FOREIGN KEY (gem_id) REFERENCES verwaltungseinheit(id); | |
879 | |
880 | |
881 | |
882 ALTER TABLE ONLY ort | |
883 ADD CONSTRAINT ort_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); | |
884 | |
885 | |
886 | |
887 ALTER TABLE ONLY ort | |
888 ADD CONSTRAINT ort_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); | |
889 | |
890 | |
891 | |
892 ALTER TABLE ONLY ort | |
893 ADD CONSTRAINT ort_ort_typ_fkey FOREIGN KEY (ort_typ) REFERENCES ort_typ(id); | |
894 | |
895 | |
896 | |
897 ALTER TABLE ONLY ort | |
898 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id); | |
899 | |
900 | |
901 | |
902 ALTER TABLE ONLY ort | |
903 ADD CONSTRAINT ort_staat_id_fkey FOREIGN KEY (staat_id) REFERENCES staat(id); | |
904 | |
905 | |
906 | |
907 ALTER TABLE ONLY pflicht_messgroesse | 862 ALTER TABLE ONLY pflicht_messgroesse |
908 ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id); | 863 ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id); |
909 | 864 |
910 | 865 |
911 | 866 |
922 ALTER TABLE ONLY proben_zusatz | 877 ALTER TABLE ONLY proben_zusatz |
923 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); | 878 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); |
924 | 879 |
925 | 880 |
926 | 881 |
927 ALTER TABLE ONLY probenehmer | |
928 ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); | |
929 | |
930 | |
931 | |
932 ALTER TABLE ONLY result | 882 ALTER TABLE ONLY result |
933 ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); | 883 ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); |
934 | 884 |
935 | 885 |
936 | 886 |