Mercurial > lada > lada-server
comparison db_schema/stammdaten_schema.sql @ 1017:dd86066d7515 schema-update
Made most constraints inline.
| author | Raimund Renkert <raimund.renkert@intevation.de> |
|---|---|
| date | Mon, 04 Jul 2016 12:09:01 +0200 |
| parents | 8912b6f1776d |
| children | 178ac8ee17c0 |
comparison
equal
deleted
inserted
replaced
| 1016:8912b6f1776d | 1017:dd86066d7515 |
|---|---|
| 104 CREATE TABLE umwelt ( | 104 CREATE TABLE umwelt ( |
| 105 id character varying(3) PRIMARY KEY, | 105 id character varying(3) PRIMARY KEY, |
| 106 beschreibung character varying(300), | 106 beschreibung character varying(300), |
| 107 umwelt_bereich character varying(80) NOT NULL, | 107 umwelt_bereich character varying(80) NOT NULL, |
| 108 meh_id integer REFERENCES mess_einheit | 108 meh_id integer REFERENCES mess_einheit |
| 109 UNIQUE (umwelt_bereich); | |
| 109 ); | 110 ); |
| 110 | 111 |
| 111 | 112 |
| 112 CREATE TABLE staat ( | 113 CREATE TABLE staat ( |
| 113 id serial PRIMARY KEY, | 114 id serial PRIMARY KEY, |
| 261 | 262 |
| 262 | 263 |
| 263 CREATE TABLE lada_user ( | 264 CREATE TABLE lada_user ( |
| 264 id serial PRIMARY KEY, | 265 id serial PRIMARY KEY, |
| 265 name character varying(80) NOT NULL | 266 name character varying(80) NOT NULL |
| 267 UNIQUE (name); | |
| 266 ); | 268 ); |
| 267 | 269 |
| 268 | 270 |
| 269 CREATE TABLE query ( | 271 CREATE TABLE query ( |
| 270 id serial PRIMARY KEY, | 272 id serial PRIMARY KEY, |
| 339 | 341 |
| 340 CREATE TABLE mg_grp ( | 342 CREATE TABLE mg_grp ( |
| 341 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe, | 343 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe, |
| 342 messgroesse_id integer NOT NULL REFERENCES messgroesse | 344 messgroesse_id integer NOT NULL REFERENCES messgroesse |
| 343 ); | 345 ); |
| 346 ALTER TABLE ONLY mg_grp | |
| 347 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id); | |
| 344 | 348 |
| 345 | 349 |
| 346 CREATE TABLE mmt_messgroesse_grp ( | 350 CREATE TABLE mmt_messgroesse_grp ( |
| 347 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe, | 351 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe, |
| 348 mmt_id character varying(2) NOT NULL REFERENCES mess_methode | 352 mmt_id character varying(2) NOT NULL REFERENCES mess_methode |
| 349 ); | 353 ); |
| 354 ALTER TABLE ONLY mmt_messgroesse_grp | |
| 355 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id); | |
| 356 | |
| 357 | |
| 350 | 358 |
| 351 | 359 |
| 352 CREATE VIEW mmt_messgroesse AS | 360 CREATE VIEW mmt_messgroesse AS |
| 353 SELECT mmt_messgroesse_grp.mmt_id, | 361 SELECT mmt_messgroesse_grp.mmt_id, |
| 354 mg_grp.messgroesse_id | 362 mg_grp.messgroesse_id |
| 421 id character varying(3) PRIMARY KEY, | 429 id character varying(3) PRIMARY KEY, |
| 422 meh_id integer REFERENCES mess_einheit, | 430 meh_id integer REFERENCES mess_einheit, |
| 423 beschreibung character varying(50) NOT NULL, | 431 beschreibung character varying(50) NOT NULL, |
| 424 zusatzwert character varying(7) NOT NULL, | 432 zusatzwert character varying(7) NOT NULL, |
| 425 eudf_keyword character varying(40) | 433 eudf_keyword character varying(40) |
| 434 UNIQUE (eudf_keyword); | |
| 426 ); | 435 ); |
| 427 | 436 |
| 428 | 437 |
| 429 CREATE TABLE probenart ( | 438 CREATE TABLE probenart ( |
| 430 id serial PRIMARY KEY, | 439 id serial PRIMARY KEY, |
| 579 ON zu.id = r.zu_id | 588 ON zu.id = r.zu_id |
| 580 ); | 589 ); |
| 581 -- Status workflow | 590 -- Status workflow |
| 582 | 591 |
| 583 | 592 |
| 584 ALTER TABLE ONLY lada_user | |
| 585 ADD CONSTRAINT lada_user_name_key UNIQUE (name); | |
| 586 | |
| 587 | |
| 588 ALTER TABLE ONLY mg_grp | |
| 589 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id); | |
| 590 | |
| 591 | |
| 592 ALTER TABLE ONLY mmt_messgroesse_grp | |
| 593 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id); | |
| 594 | |
| 595 | |
| 596 ALTER TABLE ONLY proben_zusatz | |
| 597 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword); | |
| 598 | |
| 599 | |
| 600 ALTER TABLE ONLY umwelt | |
| 601 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich); | |
| 602 | |
| 603 | |
| 604 CREATE INDEX de_vg_geom_gist ON de_vg USING gist (geom); | 593 CREATE INDEX de_vg_geom_gist ON de_vg USING gist (geom); |
| 605 | 594 |
| 606 | 595 |
| 607 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); | 596 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); |
| 608 | 597 |