Mercurial > lada > lada-server
comparison db_schema/stammdaten_schema.sql @ 1016:8912b6f1776d schema-update
Added inline FK constraints.
| author | Raimund Renkert <raimund.renkert@intevation.de> |
|---|---|
| date | Mon, 04 Jul 2016 12:05:26 +0200 |
| parents | 9f903c54141b |
| children | dd86066d7515 |
comparison
equal
deleted
inserted
replaced
| 1015:9f903c54141b | 1016:8912b6f1776d |
|---|---|
| 116 staat_iso character varying(2) NOT NULL, | 116 staat_iso character varying(2) NOT NULL, |
| 117 staat_kurz character varying(5), | 117 staat_kurz character varying(5), |
| 118 eu character(1) DEFAULT NULL::bpchar, | 118 eu character(1) DEFAULT NULL::bpchar, |
| 119 koord_x_extern character varying(22), | 119 koord_x_extern character varying(22), |
| 120 koord_y_extern character varying(22), | 120 koord_y_extern character varying(22), |
| 121 kda_id integer | 121 kda_id integer REFERENCES koordinaten_art |
| 122 ); | 122 ); |
| 123 | 123 |
| 124 | 124 |
| 125 CREATE TABLE verwaltungseinheit ( | 125 CREATE TABLE verwaltungseinheit ( |
| 126 id character varying(8) PRIMARY KEY, | 126 id character varying(8) PRIMARY KEY, |
| 127 bundesland character varying(8) NOT NULL, | 127 bundesland character varying(8) NOT NULL, |
| 128 kda_id integer, | 128 kda_id integer REFERENCES koordinaten_art, |
| 129 kreis character varying(8), | 129 kreis character varying(8), |
| 130 nuts character varying(10), | 130 nuts character varying(10), |
| 131 regbezirk character varying(8), | 131 regbezirk character varying(8), |
| 132 bezeichnung character varying(80) NOT NULL, | 132 bezeichnung character varying(80) NOT NULL, |
| 133 is_bundesland character(1) NOT NULL, | 133 is_bundesland character(1) NOT NULL, |
| 183 ); | 183 ); |
| 184 | 184 |
| 185 | 185 |
| 186 CREATE TABLE auth_lst_umw ( | 186 CREATE TABLE auth_lst_umw ( |
| 187 id serial PRIMARY KEY, | 187 id serial PRIMARY KEY, |
| 188 mst_id character varying(5), | 188 mst_id character varying(5) REFERENCES mess_stelle, |
| 189 umw_id character varying(3) | 189 umw_id character varying(3) REFERENCES umwelt |
| 190 ); | 190 ); |
| 191 | 191 |
| 192 | 192 |
| 193 CREATE TABLE datenbasis ( | 193 CREATE TABLE datenbasis ( |
| 194 id serial PRIMARY KEY, | 194 id serial PRIMARY KEY, |
| 243 s08 integer, | 243 s08 integer, |
| 244 s09 integer, | 244 s09 integer, |
| 245 s10 integer, | 245 s10 integer, |
| 246 s11 integer, | 246 s11 integer, |
| 247 s12 integer, | 247 s12 integer, |
| 248 umw_id character varying(3) NOT NULL | 248 umw_id character varying(3) NOT NULL REFERENCES umwelt |
| 249 ); | 249 ); |
| 250 | 250 |
| 251 | 251 |
| 252 CREATE TABLE deskriptoren ( | 252 CREATE TABLE deskriptoren ( |
| 253 id serial PRIMARY KEY, | 253 id serial PRIMARY KEY, |
| 254 vorgaenger integer, | 254 vorgaenger integer REFERENCES deskriptoren, |
| 255 ebene smallint, | 255 ebene smallint, |
| 256 s_xx integer, | 256 s_xx integer, |
| 257 sn smallint, | 257 sn smallint, |
| 258 beschreibung character varying(100), | 258 beschreibung character varying(100), |
| 259 bedeutung character varying(300) | 259 bedeutung character varying(300) |
| 336 ); | 336 ); |
| 337 CREATE TRIGGER letzte_aenderung_messprogramm_kategorie BEFORE UPDATE ON messprogramm_kategorie FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | 337 CREATE TRIGGER letzte_aenderung_messprogramm_kategorie BEFORE UPDATE ON messprogramm_kategorie FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); |
| 338 | 338 |
| 339 | 339 |
| 340 CREATE TABLE mg_grp ( | 340 CREATE TABLE mg_grp ( |
| 341 messgroessengruppe_id integer NOT NULL, | 341 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe, |
| 342 messgroesse_id integer NOT NULL | 342 messgroesse_id integer NOT NULL REFERENCES messgroesse |
| 343 ); | 343 ); |
| 344 | 344 |
| 345 | 345 |
| 346 CREATE TABLE mmt_messgroesse_grp ( | 346 CREATE TABLE mmt_messgroesse_grp ( |
| 347 messgroessengruppe_id integer NOT NULL, | 347 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe, |
| 348 mmt_id character varying(2) NOT NULL | 348 mmt_id character varying(2) NOT NULL REFERENCES mess_methode |
| 349 ); | 349 ); |
| 350 | 350 |
| 351 | 351 |
| 352 CREATE VIEW mmt_messgroesse AS | 352 CREATE VIEW mmt_messgroesse AS |
| 353 SELECT mmt_messgroesse_grp.mmt_id, | 353 SELECT mmt_messgroesse_grp.mmt_id, |
| 409 | 409 |
| 410 | 410 |
| 411 CREATE TABLE pflicht_messgroesse ( | 411 CREATE TABLE pflicht_messgroesse ( |
| 412 id serial PRIMARY KEY, | 412 id serial PRIMARY KEY, |
| 413 messgroesse_id integer, | 413 messgroesse_id integer, |
| 414 mmt_id character varying(2), | 414 mmt_id character varying(2) REFERENCES mess_methode, |
| 415 umw_id character varying(3), | 415 umw_id character varying(3) REFERENCES umwelt, |
| 416 datenbasis_id smallint NOT NULL | 416 datenbasis_id smallint NOT NULL REFERENCES datenbasis |
| 417 ); | 417 ); |
| 418 | 418 |
| 419 | 419 |
| 420 CREATE TABLE proben_zusatz ( | 420 CREATE TABLE proben_zusatz ( |
| 421 id character varying(3) PRIMARY KEY, | 421 id character varying(3) PRIMARY KEY, |
| 422 meh_id integer, | 422 meh_id integer REFERENCES mess_einheit, |
| 423 beschreibung character varying(50) NOT NULL, | 423 beschreibung character varying(50) NOT NULL, |
| 424 zusatzwert character varying(7) NOT NULL, | 424 zusatzwert character varying(7) NOT NULL, |
| 425 eudf_keyword character varying(40) | 425 eudf_keyword character varying(40) |
| 426 ); | 426 ); |
| 427 | 427 |