Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 1097:186d602e031a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged branch schema-update into default.
author Tom Gottfried <tom@intevation.de>
date Fri, 14 Oct 2016 18:17:42 +0200
parents 6499f2410c42
children 7061388e0af7
comparison
equal deleted inserted replaced
1096:565c8a67034d 1097:186d602e031a
9 SET standard_conforming_strings = on; 9 SET standard_conforming_strings = on;
10 SET check_function_bodies = false; 10 SET check_function_bodies = false;
11 SET client_min_messages = warning; 11 SET client_min_messages = warning;
12 12
13 -- 13 --
14 -- Name: bund; Type: SCHEMA; Schema: -; Owner: -
15 --
16
17 CREATE SCHEMA bund;
18
19
20 --
21 -- Name: land; Type: SCHEMA; Schema: -; Owner: - 14 -- Name: land; Type: SCHEMA; Schema: -; Owner: -
22 -- 15 --
23 16
24 CREATE SCHEMA land; 17 CREATE SCHEMA land;
25 18
26 19 SET search_path = land, pg_catalog;
27 SET search_path = bund, pg_catalog; 20
28
29 --
30 -- Name: update_time_status(); Type: FUNCTION; Schema: bund; Owner: -
31 --
32
33 CREATE FUNCTION update_time_status() RETURNS trigger
34 LANGUAGE plpgsql
35 AS $$
36 BEGIN
37 NEW.tree_modified = now();
38 RETURN NEW;
39 END;
40 $$;
41 21
42 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger 22 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger
43 LANGUAGE plpgsql 23 LANGUAGE plpgsql
44 AS $$ 24 AS $$
45 BEGIN 25 BEGIN
46 NEW.letzte_aenderung = now(); 26 NEW.letzte_aenderung = now();
47 RETURN NEW; 27 RETURN NEW;
48 END; 28 END;
49 $$; 29 $$;
50 30
51 SET search_path = land, pg_catalog; 31
52 32 --
53 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger 33 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
54 LANGUAGE plpgsql 34 --
55 AS $$ 35
56 BEGIN 36 CREATE FUNCTION update_tree_modified() RETURNS trigger
57 NEW.letzte_aenderung = now();
58 RETURN NEW;
59 END;
60 $$;
61
62 --
63 -- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: -
64 --
65
66 CREATE FUNCTION is_probe_fertig(i1 integer) RETURNS boolean
67 LANGUAGE plpgsql STABLE SECURITY DEFINER
68 AS $_$
69 DECLARE result BOOLEAN;
70 BEGIN
71 SELECT (count(id) > 0) INTO result from land.messung where probe_id = $1 and fertig = TRUE;
72 RETURN result;
73 END;
74 $_$;
75
76
77 --
78 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
79 --
80
81 CREATE FUNCTION update_time_messung() RETURNS trigger
82 LANGUAGE plpgsql
83 AS $$
84 BEGIN
85 RAISE NOTICE 'messung is %',NEW.id;
86 NEW.tree_modified = now();
87 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
88 UPDATE bund.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id;
89 RETURN NEW;
90 END;
91 $$;
92
93
94 --
95 -- Name: update_time_messwert(); Type: FUNCTION; Schema: land; Owner: -
96 --
97
98 CREATE FUNCTION update_time_messwert() RETURNS trigger
99 LANGUAGE plpgsql 37 LANGUAGE plpgsql
100 AS $$ 38 AS $$
101 BEGIN 39 BEGIN
102 NEW.tree_modified = now(); 40 NEW.tree_modified = now();
103 RETURN NEW; 41 RETURN NEW;
104 END; 42 END;
105 $$; 43 $$;
106 44
107 45
108 -- 46 --
109 -- Name: update_time_ort(); Type: FUNCTION; Schema: land; Owner: - 47 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
110 -- 48 --
111 49
112 CREATE FUNCTION update_time_ort() RETURNS trigger 50 CREATE FUNCTION update_tree_modified_messung() RETURNS trigger
113 LANGUAGE plpgsql 51 LANGUAGE plpgsql
114 AS $$ 52 AS $$
115 BEGIN 53 BEGIN
54 RAISE NOTICE 'messung is %',NEW.id;
116 NEW.tree_modified = now(); 55 NEW.tree_modified = now();
56 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
57 UPDATE land.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id;
117 RETURN NEW; 58 RETURN NEW;
118 END; 59 END;
119 $$; 60 $$;
120 61
121 62
122 -- 63 --
123 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: - 64 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
124 -- 65 --
125 66
126 CREATE FUNCTION update_time_probe() RETURNS trigger 67 CREATE FUNCTION update_tree_modified_probe() RETURNS trigger
127 LANGUAGE plpgsql 68 LANGUAGE plpgsql
128 AS $$ 69 AS $$
129 BEGIN 70 BEGIN
130 RAISE NOTICE 'probe is %',NEW.id; 71 RAISE NOTICE 'probe is %',NEW.id;
131 NEW.tree_modified = now(); 72 NEW.tree_modified = now();
136 RETURN NEW; 77 RETURN NEW;
137 END; 78 END;
138 $$; 79 $$;
139 80
140 81
141 --
142 -- Name: update_time_zusatzwert(); Type: FUNCTION; Schema: land; Owner: -
143 --
144
145 CREATE FUNCTION update_time_zusatzwert() RETURNS trigger
146 LANGUAGE plpgsql
147 AS $$
148 BEGIN
149 NEW.tree_modified = now();
150 RETURN NEW;
151 END;
152 $$;
153
154
155 SET search_path = bund, pg_catalog;
156
157 --
158 -- Name: kommentar_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
159 --
160
161 CREATE SEQUENCE kommentar_id_seq
162 START WITH 1
163 INCREMENT BY 1
164 NO MINVALUE
165 NO MAXVALUE
166 CACHE 1;
167
168
169 SET default_tablespace = ''; 82 SET default_tablespace = '';
170 83
171 SET default_with_oids = false; 84 SET default_with_oids = false;
172 85
173 -- 86
174 -- Name: kommentar; Type: TABLE; Schema: bund; Owner: -; Tablespace: 87 --
175 -- 88 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: land; Owner: -
176
177 CREATE TABLE kommentar (
178 id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL,
179 erzeuger character varying(5) NOT NULL,
180 datum timestamp without time zone DEFAULT now(),
181 text character varying(1024)
182 );
183
184
185 --
186 -- Name: kommentar_m; Type: TABLE; Schema: bund; Owner: -; Tablespace:
187 --
188
189 CREATE TABLE kommentar_m (
190 messungs_id integer NOT NULL
191 )
192 INHERITS (kommentar);
193
194
195 --
196 -- Name: kommentar_p; Type: TABLE; Schema: bund; Owner: -; Tablespace:
197 --
198
199 CREATE TABLE kommentar_p (
200 probe_id integer NOT NULL
201 )
202 INHERITS (kommentar);
203
204
205 --
206 -- Name: list; Type: TABLE; Schema: bund; Owner: -; Tablespace:
207 --
208
209 CREATE TABLE list (
210 id integer NOT NULL,
211 typ character(1) NOT NULL,
212 bezeichnung character varying(20) NOT NULL,
213 beschreibuing character varying(512),
214 letzte_aenderung timestamp with time zone NOT NULL,
215 gueltig_bis timestamp with time zone
216 );
217 CREATE TRIGGER letzte_aenderung_list BEFORE UPDATE ON list FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
218
219
220 --
221 -- Name: list_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
222 --
223
224 CREATE SEQUENCE list_id_seq
225 START WITH 1
226 INCREMENT BY 1
227 NO MINVALUE
228 NO MAXVALUE
229 CACHE 1;
230
231
232 --
233 -- Name: list_id_seq; Type: SEQUENCE OWNED BY; Schema: bund; Owner: -
234 --
235
236 ALTER SEQUENCE list_id_seq OWNED BY list.id;
237
238
239 --
240 -- Name: list_zuordnung; Type: TABLE; Schema: bund; Owner: -; Tablespace:
241 --
242
243 CREATE TABLE list_zuordnung (
244 list_id integer NOT NULL,
245 probe_id integer
246 );
247
248
249 --
250 -- Name: messung_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
251 --
252
253 CREATE SEQUENCE messung_id_seq
254 START WITH 1
255 INCREMENT BY 1
256 NO MINVALUE
257 NO MAXVALUE
258 CACHE 1;
259
260
261 --
262 -- Name: messung; Type: TABLE; Schema: bund; Owner: -; Tablespace:
263 --
264
265 CREATE TABLE messung (
266 id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL,
267 probe_id integer NOT NULL,
268 nebenproben_nr character varying(10),
269 mmt_id character varying(2) NOT NULL,
270 messdauer integer,
271 messzeitpunkt timestamp with time zone,
272 fertig boolean DEFAULT false NOT NULL,
273 status integer,
274 letzte_aenderung timestamp without time zone DEFAULT now()
275 );
276 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
277
278
279 --
280 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: bund; Owner: -
281 -- 89 --
282 90
283 CREATE SEQUENCE messung_messung_id_alt_seq 91 CREATE SEQUENCE messung_messung_id_alt_seq
284 START WITH 1 92 START WITH 1
285 INCREMENT BY 1 93 INCREMENT BY 1
287 NO MAXVALUE 95 NO MAXVALUE
288 CACHE 1; 96 CACHE 1;
289 97
290 98
291 -- 99 --
292 -- Name: messung_messungs_id_seq; Type: SEQUENCE; Schema: bund; Owner: - 100 -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: land; Owner: -
293 --
294
295 CREATE SEQUENCE messung_messungs_id_seq
296 START WITH 1
297 INCREMENT BY 1
298 NO MINVALUE
299 NO MAXVALUE
300 CACHE 1;
301
302
303 --
304 -- Name: messwert_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
305 --
306
307 CREATE SEQUENCE messwert_id_seq
308 START WITH 1
309 INCREMENT BY 1
310 NO MINVALUE
311 NO MAXVALUE
312 CACHE 1;
313
314
315 --
316 -- Name: messwert; Type: TABLE; Schema: bund; Owner: -; Tablespace:
317 --
318
319 CREATE TABLE messwert (
320 id integer DEFAULT nextval('messwert_id_seq'::regclass) NOT NULL,
321 messungs_id integer NOT NULL,
322 messgroesse_id integer NOT NULL,
323 messwert_nwg character varying(1),
324 messwert double precision NOT NULL,
325 messfehler real,
326 nwg_zu_messwert double precision,
327 meh_id smallint NOT NULL,
328 grenzwertueberschreitung boolean DEFAULT false,
329 letzte_aenderung timestamp without time zone DEFAULT now()
330 );
331 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
332
333
334 --
335 -- Name: ort_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
336 --
337
338 CREATE SEQUENCE ort_id_seq
339 START WITH 1
340 INCREMENT BY 1
341 NO MINVALUE
342 NO MAXVALUE
343 CACHE 1;
344
345
346 --
347 -- Name: ortszuordnung; Type: TABLE; Schema: bund; Owner: -; Tablespace:
348 --
349
350 CREATE TABLE ortszuordnung (
351 id integer DEFAULT nextval('ort_id_seq'::regclass) NOT NULL,
352 probe_id integer NOT NULL,
353 ort_id bigint NOT NULL,
354 ortszuordnung_typ character varying(1),
355 ortszusatztext character varying(100),
356 letzte_aenderung timestamp without time zone DEFAULT now()
357 );
358 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
359
360
361 --
362 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: bund; Owner: -
363 --
364
365 COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
366
367
368 --
369 -- Name: probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
370 --
371
372 CREATE SEQUENCE probe_id_seq
373 START WITH 1
374 INCREMENT BY 1
375 NO MINVALUE
376 NO MAXVALUE
377 CACHE 1;
378
379
380 --
381 -- Name: probe; Type: TABLE; Schema: bund; Owner: -; Tablespace:
382 --
383
384 CREATE TABLE probe (
385 id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass),
386 test boolean DEFAULT false NOT NULL,
387 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber,
388 mst_id character varying(5) NOT NULL
389 REFERENCES stammdaten.mess_stelle,
390 labor_mst_id character varying(5) NOT NULL
391 REFERENCES stammdaten.mess_stelle,
392 hauptproben_nr character varying(20),
393 datenbasis_id smallint REFERENCES stammdaten.datenbasis,
394 ba_id integer,
395 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
396 media_desk character varying(100),
397 media character varying(100),
398 umw_id character varying(3) REFERENCES stammdaten.umwelt,
399 probeentnahme_beginn timestamp with time zone,
400 probeentnahme_ende timestamp with time zone,
401 mittelungsdauer bigint,
402 letzte_aenderung timestamp without time zone DEFAULT now(),
403 UNIQUE (mst_id, hauptproben_nr)
404 );
405
406 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
407
408 --
409 -- Name: COLUMN probe.id; Type: COMMENT; Schema: bund; Owner: -
410 --
411
412 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel';
413
414
415 --
416 -- Name: COLUMN probe.test; Type: COMMENT; Schema: bund; Owner: -
417 --
418
419 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
420
421
422 --
423 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: bund; Owner: -
424 --
425
426 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
427
428
429 --
430 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: bund; Owner: -
431 --
432
433 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
434
435
436 --
437 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: bund; Owner: -
438 --
439
440 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel';
441
442
443 --
444 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: bund; Owner: -
445 --
446
447 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
448
449
450 --
451 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: bund; Owner: -
452 --
453
454 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
455
456
457 --
458 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: bund; Owner: -
459 --
460
461 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
462
463
464 --
465 -- Name: COLUMN probe.media; Type: COMMENT; Schema: bund; Owner: -
466 --
467
468 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
469
470
471 --
472 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: bund; Owner: -
473 --
474
475 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
476
477
478 --
479 -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
480 -- 101 --
481 102
482 CREATE SEQUENCE probe_probe_id_seq 103 CREATE SEQUENCE probe_probe_id_seq
483 START WITH 1 104 START WITH 1
484 INCREMENT BY 1 105 INCREMENT BY 1
486 NO MAXVALUE 107 NO MAXVALUE
487 CACHE 1; 108 CACHE 1;
488 109
489 110
490 -- 111 --
491 -- Name: status_protokoll; Type: TABLE; Schema: bund; Owner: -; Tablespace:
492 --
493
494 CREATE TABLE status_protokoll (
495 status_stufe integer NOT NULL,
496 status_wert integer NOT NULL,
497 tree_modified timestamp without time zone DEFAULT now()
498 )
499 INHERITS (kommentar_m);
500
501
502 --
503 -- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
504 --
505
506 CREATE SEQUENCE zusatz_wert_id_seq
507 START WITH 1
508 INCREMENT BY 1
509 NO MINVALUE
510 NO MAXVALUE
511 CACHE 1;
512
513
514 --
515 -- Name: zusatz_wert; Type: TABLE; Schema: bund; Owner: -; Tablespace:
516 --
517
518 CREATE TABLE zusatz_wert (
519 id integer DEFAULT nextval('zusatz_wert_id_seq'::regclass) NOT NULL,
520 probe_id integer NOT NULL,
521 pzs_id character varying(3) NOT NULL,
522 messwert_pzs double precision,
523 messfehler real,
524 letzte_aenderung timestamp without time zone DEFAULT now(),
525 nwg_zu_messwert double precision
526 );
527 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
528
529
530 SET search_path = land, pg_catalog;
531
532 --
533 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
534 --
535
536 CREATE TABLE kommentar_m (
537 )
538 INHERITS (bund.kommentar_m);
539
540
541 --
542 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
543 --
544
545 CREATE TABLE kommentar_p (
546 )
547 INHERITS (bund.kommentar_p);
548
549
550 --
551 -- Name: messprogramm_id_seq; Type: SEQUENCE; Schema: land; Owner: -
552 --
553
554 CREATE SEQUENCE messprogramm_id_seq
555 START WITH 1
556 INCREMENT BY 1
557 NO MINVALUE
558 NO MAXVALUE
559 CACHE 1;
560
561
562 --
563 -- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace: 112 -- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace:
564 -- 113 --
565 114
566 CREATE TABLE messprogramm ( 115 CREATE TABLE messprogramm (
567 id integer PRIMARY KEY DEFAULT nextval('messprogramm_id_seq'::regclass), 116 id serial PRIMARY KEY,
568 name character varying(256), 117 kommentar character varying(1000),
569 test boolean DEFAULT false NOT NULL, 118 test boolean DEFAULT false NOT NULL,
570 netzbetreiber_id character varying(2) NOT NULL
571 REFERENCES stammdaten.netz_betreiber,
572 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, 119 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
573 labor_mst_id character varying(5) NOT NULL 120 labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
574 REFERENCES stammdaten.mess_stelle,
575 datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis, 121 datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis,
576 ba_id integer DEFAULT 1, 122 ba_id integer DEFAULT 0 REFERENCES stammdaten.betriebsart,
577 gem_id character varying(8), 123 gem_id character varying(8) REFERENCES stammdaten.verwaltungseinheit,
578 ort_id integer NOT NULL REFERENCES stammdaten.ort, 124 ort_id integer NOT NULL REFERENCES stammdaten.ort,
579 media_desk character varying(100), 125 media_desk character varying(100),
580 umw_id character varying(3) REFERENCES stammdaten.umwelt, 126 umw_id character varying(3) REFERENCES stammdaten.umwelt,
581 probenart_id integer NOT NULL REFERENCES stammdaten.probenart, 127 probenart_id integer NOT NULL REFERENCES stammdaten.probenart,
582 probenintervall character varying(2) NOT NULL, 128 probenintervall character varying(2) NOT NULL,
583 teilintervall_von integer NOT NULL, 129 teilintervall_von integer NOT NULL,
584 teilintervall_bis integer NOT NULL, 130 teilintervall_bis integer NOT NULL,
585 intervall_offset integer NOT NULL DEFAULT 0, 131 intervall_offset integer NOT NULL DEFAULT 0,
586 gueltig_von integer NOT NULL CHECK(gueltig_von BETWEEN 1 AND 365), 132 gueltig_von integer NOT NULL CHECK(gueltig_von BETWEEN 1 AND 365),
587 gueltig_bis integer NOT NULL CHECK(gueltig_bis BETWEEN 1 AND 365), 133 gueltig_bis integer NOT NULL CHECK(gueltig_bis BETWEEN 1 AND 365),
588 probe_nehmer_id integer, 134 probe_nehmer_id integer REFERENCES stammdaten.probenehmer,
589 probe_kommentar character varying(80), 135 probe_kommentar character varying(80),
590 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL, 136 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL,
591 CHECK (probenintervall = 'J' 137 CHECK (probenintervall = 'J'
592 AND teilintervall_von BETWEEN gueltig_von AND gueltig_bis 138 AND teilintervall_von BETWEEN gueltig_von AND gueltig_bis
593 AND teilintervall_bis BETWEEN gueltig_von AND gueltig_bis 139 AND teilintervall_bis BETWEEN gueltig_von AND gueltig_bis
623 ), 169 ),
624 CHECK (teilintervall_von <= teilintervall_bis) 170 CHECK (teilintervall_von <= teilintervall_bis)
625 ); 171 );
626 CREATE TRIGGER letzte_aenderung_messprogramm BEFORE UPDATE ON messprogramm FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 172 CREATE TRIGGER letzte_aenderung_messprogramm BEFORE UPDATE ON messprogramm FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
627 173
628 --
629 -- Name: messprogramm_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
630 --
631
632 ALTER SEQUENCE messprogramm_id_seq OWNED BY messprogramm.id;
633
634
635 --
636 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
637 --
638
639 COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
640
641 174
642 -- 175 --
643 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace: 176 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
644 -- 177 --
645 178
646 CREATE TABLE messprogramm_mmt ( 179 CREATE TABLE messprogramm_mmt (
647 id integer NOT NULL, 180 id serial PRIMARY KEY,
648 messprogramm_id integer NOT NULL, 181 messprogramm_id integer NOT NULL REFERENCES messprogramm ON DELETE CASCADE,
649 mmt_id character varying(2) NOT NULL, 182 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode,
650 messgroessen integer[], 183 messgroessen integer[],
651 letzte_aenderung timestamp without time zone DEFAULT now() 184 letzte_aenderung timestamp without time zone DEFAULT now()
652 ); 185 );
653 CREATE TRIGGER letzte_aenderung_messprogramm_mmt BEFORE UPDATE ON messprogramm_mmt FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 186 CREATE TRIGGER letzte_aenderung_messprogramm_mmt BEFORE UPDATE ON messprogramm_mmt FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
654 187
655 188
656 -- 189 --
657 -- Name: messprogramm_mmt_id_seq; Type: SEQUENCE; Schema: land; Owner: -
658 --
659
660 CREATE SEQUENCE messprogramm_mmt_id_seq
661 START WITH 1
662 INCREMENT BY 1
663 NO MINVALUE
664 NO MAXVALUE
665 CACHE 1;
666
667
668 --
669 -- Name: messprogramm_mmt_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
670 --
671
672 ALTER SEQUENCE messprogramm_mmt_id_seq OWNED BY messprogramm_mmt.id;
673
674
675 --
676 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
677 --
678
679 CREATE TABLE messung (
680 geplant boolean DEFAULT false NOT NULL,
681 tree_modified timestamp without time zone DEFAULT now()
682 )
683 INHERITS (bund.messung);
684
685
686 --
687 -- Name: messung_translation; Type: TABLE; Schema: land; Owner: -; Tablespace:
688 --
689
690 CREATE TABLE messung_translation (
691 id integer NOT NULL,
692 messungs_id integer NOT NULL,
693 messungs_id_alt integer DEFAULT nextval('bund.messung_messung_id_alt_seq'::regclass) NOT NULL
694 );
695
696
697 --
698 -- Name: messung_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: -
699 --
700
701 CREATE SEQUENCE messung_translation_id_seq
702 START WITH 1
703 INCREMENT BY 1
704 NO MINVALUE
705 NO MAXVALUE
706 CACHE 1;
707
708
709 --
710 -- Name: messung_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
711 --
712
713 ALTER SEQUENCE messung_translation_id_seq OWNED BY messung_translation.id;
714
715
716 --
717 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
718 --
719
720 CREATE TABLE messwert (
721 tree_modified timestamp without time zone DEFAULT now()
722 )
723 INHERITS (bund.messwert);
724
725
726 --
727 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
728 --
729
730 CREATE TABLE ortszuordnung (
731 tree_modified timestamp without time zone DEFAULT now()
732 )
733 INHERITS (bund.ortszuordnung);
734
735
736 --
737 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace: 190 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
738 -- 191 --
739 192
740 CREATE TABLE probe ( 193 CREATE TABLE probe (
741 erzeuger_id integer, 194 id serial PRIMARY KEY,
742 probe_nehmer_id integer, 195 id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('land.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL,
743 mp_kat character(1), 196 test boolean DEFAULT false NOT NULL,
744 mpl_id character varying(3), 197 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
745 mpr_id integer, 198 labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
199 hauptproben_nr character varying(20),
200 datenbasis_id smallint REFERENCES stammdaten.datenbasis,
201 ba_id integer REFERENCES stammdaten.betriebsart,
202 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
203 media_desk character varying(100),
204 media character varying(100),
205 umw_id character varying(3) REFERENCES stammdaten.umwelt,
206 probeentnahme_beginn timestamp with time zone,
207 probeentnahme_ende timestamp with time zone,
208 mittelungsdauer bigint,
209 letzte_aenderung timestamp without time zone DEFAULT now(),
210 erzeuger_id integer REFERENCES stammdaten.datensatz_erzeuger,
211 probe_nehmer_id integer REFERENCES stammdaten.probenehmer,
212 mpl_id integer REFERENCES stammdaten.messprogramm_kategorie,
213 mpr_id integer REFERENCES messprogramm,
746 solldatum_beginn timestamp without time zone, 214 solldatum_beginn timestamp without time zone,
747 solldatum_ende timestamp without time zone, 215 solldatum_ende timestamp without time zone,
748 tree_modified timestamp without time zone DEFAULT now(), 216 tree_modified timestamp without time zone DEFAULT now(),
217 UNIQUE (mst_id, hauptproben_nr, id_alt),
749 CHECK(solldatum_beginn <= solldatum_ende) 218 CHECK(solldatum_beginn <= solldatum_ende)
750 ) 219 );
751 INHERITS (bund.probe); 220 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
752 221 CREATE TRIGGER tree_modified_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_probe();
753 ALTER TABLE ONLY probe 222
754 ADD CONSTRAINT probe_pkey PRIMARY KEY (id); 223
755 224 --
756 ALTER TABLE ONLY probe 225 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
757 ADD CONSTRAINT probe_datenbasis_id_fkey 226 --
758 FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id); 227
759 228 CREATE TABLE kommentar_p (
760 ALTER TABLE ONLY probe 229 id serial PRIMARY KEY,
761 ADD CONSTRAINT probe_mst_id_fkey 230 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
762 FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id); 231 datum timestamp without time zone DEFAULT now(),
763 232 text character varying(1024),
764 ALTER TABLE ONLY probe 233 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE
765 ADD CONSTRAINT probe_labor_mst_id_fkey 234 );
766 FOREIGN KEY (labor_mst_id) REFERENCES stammdaten.mess_stelle(id); 235
767 236
768 ALTER TABLE ONLY probe 237 --
769 ADD CONSTRAINT probe_netzbetreiber_id_fkey 238 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
770 FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id); 239 --
771 240
772 ALTER TABLE ONLY probe 241 CREATE TABLE ortszuordnung (
773 ADD CONSTRAINT probe_probenart_id_fkey 242 id serial PRIMARY KEY,
774 FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id); 243 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
775 244 ort_id bigint NOT NULL REFERENCES stammdaten.ort,
776 ALTER TABLE ONLY probe 245 ortszuordnung_typ character varying(1) REFERENCES stammdaten.ortszuordnung_typ,
777 ADD CONSTRAINT probe_umw_id_fkey 246 ortszusatztext character varying(100),
778 FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id); 247 letzte_aenderung timestamp without time zone DEFAULT now(),
779 248 tree_modified timestamp without time zone DEFAULT now()
780 249 );
781 -- 250 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
782 -- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace: 251 CREATE TRIGGER tree_modified_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
783 -- 252
784 253
785 CREATE TABLE probe_translation ( 254 --
786 id integer NOT NULL, 255 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
787 probe_id integer NOT NULL, 256 --
788 probe_id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('bund.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL 257
789 ); 258 CREATE TABLE zusatz_wert (
790 259 id serial PRIMARY KEY,
791 260 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
792 -- 261 pzs_id character varying(3) NOT NULL REFERENCES stammdaten.proben_zusatz,
793 -- Name: probe_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: - 262 messwert_pzs double precision,
794 -- 263 messfehler real,
795 264 letzte_aenderung timestamp without time zone DEFAULT now(),
796 CREATE SEQUENCE probe_translation_id_seq 265 nwg_zu_messwert double precision,
797 START WITH 1 266 tree_modified timestamp without time zone DEFAULT now(),
798 INCREMENT BY 1 267 UNIQUE (probe_id, pzs_id)
799 NO MINVALUE 268 );
800 NO MAXVALUE 269 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
801 CACHE 1; 270 CREATE TRIGGER tree_modified_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
802 271
803 272
804 -- 273 --
805 -- Name: probe_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: - 274 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
806 -- 275 --
807 276
808 ALTER SEQUENCE probe_translation_id_seq OWNED BY probe_translation.id; 277 CREATE TABLE messung (
278 id serial PRIMARY KEY,
279 id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL,
280 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
281 nebenproben_nr character varying(10),
282 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode ON DELETE CASCADE,
283 messdauer integer,
284 messzeitpunkt timestamp with time zone,
285 fertig boolean DEFAULT false NOT NULL,
286 status integer,
287 letzte_aenderung timestamp without time zone DEFAULT now(),
288 geplant boolean DEFAULT false NOT NULL,
289 tree_modified timestamp without time zone DEFAULT now()
290 );
291 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
292 CREATE TRIGGER tree_modified_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_messung();
293
294
295 --
296 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
297 --
298
299 CREATE TABLE kommentar_m (
300 id serial PRIMARY KEY,
301 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
302 datum timestamp without time zone DEFAULT now(),
303 text character varying(1024),
304 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE
305 );
306
307
308 --
309 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
310 --
311
312 CREATE TABLE messwert (
313 id serial PRIMARY KEY,
314 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
315 messgroesse_id integer NOT NULL REFERENCES stammdaten.messgroesse,
316 messwert_nwg character varying(1),
317 messwert double precision NOT NULL,
318 messfehler real,
319 nwg_zu_messwert double precision,
320 meh_id smallint NOT NULL REFERENCES stammdaten.mess_einheit,
321 grenzwertueberschreitung boolean DEFAULT false,
322 letzte_aenderung timestamp without time zone DEFAULT now(),
323 tree_modified timestamp without time zone DEFAULT now(),
324 UNIQUE (messungs_id, messgroesse_id)
325 );
326 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
327 CREATE TRIGGER tree_modified_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
809 328
810 329
811 -- 330 --
812 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace: 331 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
813 -- 332 --
814 333
815 CREATE TABLE status_protokoll ( 334 CREATE TABLE status_protokoll (
816 ) 335 id serial PRIMARY KEY,
817 INHERITS (bund.status_protokoll); 336 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
818 337 datum timestamp without time zone DEFAULT now(),
819 338 text character varying(1024),
820 -- 339 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
821 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace: 340 status_kombi integer NOT NULL REFERENCES stammdaten.status_kombi,
822 --
823
824 CREATE TABLE zusatz_wert (
825 tree_modified timestamp without time zone DEFAULT now() 341 tree_modified timestamp without time zone DEFAULT now()
826 ) 342 );
827 INHERITS (bund.zusatz_wert); 343 CREATE TRIGGER tree_modified_status_protokoll BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
828
829
830 SET search_path = bund, pg_catalog;
831
832 --
833 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
834 --
835
836 ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
837
838
839 --
840 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
841 --
842
843 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now();
844
845
846 --
847 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
848 --
849
850 ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
851
852
853 --
854 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
855 --
856
857 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now();
858
859
860 --
861 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
862 --
863
864 ALTER TABLE ONLY list ALTER COLUMN id SET DEFAULT nextval('list_id_seq'::regclass);
865
866
867 --
868 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
869 --
870
871 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
872
873
874 --
875 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
876 --
877
878 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now();
879
880
881 SET search_path = land, pg_catalog;
882
883 --
884 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
885 --
886
887 ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
888
889
890 --
891 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
892 --
893
894 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now();
895
896
897 --
898 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
899 --
900
901 ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
902
903
904 --
905 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
906 --
907
908 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now();
909
910
911 --
912 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
913 --
914
915 ALTER TABLE ONLY messprogramm_mmt ALTER COLUMN id SET DEFAULT nextval('messprogramm_mmt_id_seq'::regclass);
916
917
918 --
919 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
920 --
921
922 ALTER TABLE ONLY messung ALTER COLUMN id SET DEFAULT nextval('bund.messung_id_seq'::regclass);
923
924
925 --
926 -- Name: fertig; Type: DEFAULT; Schema: land; Owner: -
927 --
928
929 ALTER TABLE ONLY messung ALTER COLUMN fertig SET DEFAULT false;
930
931
932 --
933 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
934 --
935
936 ALTER TABLE ONLY messung ALTER COLUMN letzte_aenderung SET DEFAULT now();
937
938
939 --
940 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
941 --
942
943 ALTER TABLE ONLY messung_translation ALTER COLUMN id SET DEFAULT nextval('messung_translation_id_seq'::regclass);
944
945
946 --
947 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
948 --
949
950 ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('bund.messwert_id_seq'::regclass);
951
952
953 --
954 -- Name: grenzwertueberschreitung; Type: DEFAULT; Schema: land; Owner: -
955 --
956
957 ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false;
958
959
960 --
961 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
962 --
963
964 ALTER TABLE ONLY messwert ALTER COLUMN letzte_aenderung SET DEFAULT now();
965
966
967 --
968 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
969 --
970
971 ALTER TABLE ONLY ortszuordnung ALTER COLUMN id SET DEFAULT nextval('bund.ort_id_seq'::regclass);
972
973
974 --
975 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
976 --
977
978 ALTER TABLE ONLY ortszuordnung ALTER COLUMN letzte_aenderung SET DEFAULT now();
979
980
981 --
982 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
983 --
984
985 ALTER TABLE ONLY probe_translation ALTER COLUMN id SET DEFAULT nextval('probe_translation_id_seq'::regclass);
986
987
988 --
989 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
990 --
991
992 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
993
994
995 --
996 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
997 --
998
999 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now();
1000
1001
1002 --
1003 -- Name: tree_modified; Type: DEFAULT; Schema: land; Owner: -
1004 --
1005
1006 ALTER TABLE ONLY status_protokoll ALTER COLUMN tree_modified SET DEFAULT now();
1007
1008
1009 --
1010 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1011 --
1012
1013 ALTER TABLE ONLY zusatz_wert ALTER COLUMN id SET DEFAULT nextval('bund.zusatz_wert_id_seq'::regclass);
1014
1015
1016 --
1017 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
1018 --
1019
1020 ALTER TABLE ONLY zusatz_wert ALTER COLUMN letzte_aenderung SET DEFAULT now();
1021
1022
1023 SET search_path = bund, pg_catalog;
1024
1025 --
1026 -- Name: kommentar_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1027 --
1028
1029 ALTER TABLE ONLY kommentar
1030 ADD CONSTRAINT kommentar_pkey PRIMARY KEY (id);
1031
1032
1033 --
1034 -- Name: list_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1035 --
1036
1037 ALTER TABLE ONLY list
1038 ADD CONSTRAINT list_pkey PRIMARY KEY (id);
1039
1040
1041 --
1042 -- Name: messung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1043 --
1044
1045 ALTER TABLE ONLY messung
1046 ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
1047
1048
1049 --
1050 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1051 --
1052
1053 ALTER TABLE ONLY messwert
1054 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id);
1055
1056
1057 --
1058 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1059 --
1060
1061 ALTER TABLE ONLY messwert
1062 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id);
1063
1064
1065 --
1066 -- Name: ortszuordnung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1067 --
1068
1069 ALTER TABLE ONLY ortszuordnung
1070 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id);
1071
1072
1073 --
1074 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1075 --
1076
1077 ALTER TABLE ONLY status_protokoll
1078 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
1079
1080
1081 --
1082 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1083 --
1084
1085 ALTER TABLE ONLY zusatz_wert
1086 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id);
1087
1088
1089 --
1090 -- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1091 --
1092
1093 ALTER TABLE ONLY zusatz_wert
1094 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id);
1095
1096
1097 SET search_path = land, pg_catalog;
1098
1099 --
1100 -- Name: kommentar_m_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1101 --
1102
1103 ALTER TABLE ONLY kommentar_m
1104 ADD CONSTRAINT kommentar_m_pkey PRIMARY KEY (id);
1105
1106
1107 --
1108 -- Name: kommentar_p_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1109 --
1110
1111 ALTER TABLE ONLY kommentar_p
1112 ADD CONSTRAINT kommentar_p_pkey PRIMARY KEY (id);
1113
1114
1115 --
1116 -- Name: messprogramm_mmt_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1117 --
1118
1119 ALTER TABLE ONLY messprogramm_mmt
1120 ADD CONSTRAINT messprogramm_mmt_pkey PRIMARY KEY (id);
1121
1122
1123 --
1124 -- Name: messung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1125 --
1126
1127 ALTER TABLE ONLY messung
1128 ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
1129
1130
1131 --
1132 -- Name: messung_translation_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1133 --
1134
1135 ALTER TABLE ONLY messung_translation
1136 ADD CONSTRAINT messung_translation_messungs_id_key UNIQUE (messungs_id);
1137
1138
1139 --
1140 -- Name: messung_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1141 --
1142
1143 ALTER TABLE ONLY messung_translation
1144 ADD CONSTRAINT messung_translation_pkey PRIMARY KEY (id);
1145
1146
1147 --
1148 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1149 --
1150
1151 ALTER TABLE ONLY messwert
1152 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id);
1153
1154
1155 --
1156 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1157 --
1158
1159 ALTER TABLE ONLY messwert
1160 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id);
1161
1162
1163 --
1164 -- Name: ortszuordnung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1165 --
1166
1167 ALTER TABLE ONLY ortszuordnung
1168 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id);
1169
1170
1171 --
1172 -- Name: probe_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1173 --
1174
1175 ALTER TABLE ONLY probe_translation
1176 ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id);
1177
1178
1179 --
1180 -- Name: probe_translation_probe_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1181 --
1182
1183 ALTER TABLE ONLY probe_translation
1184 ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id);
1185
1186
1187 --
1188 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1189 --
1190
1191 ALTER TABLE ONLY status_protokoll
1192 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
1193
1194
1195 --
1196 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1197 --
1198
1199 ALTER TABLE ONLY zusatz_wert
1200 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id);
1201
1202
1203 --
1204 -- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1205 --
1206
1207 ALTER TABLE ONLY zusatz_wert
1208 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id);
1209
1210
1211 SET search_path = bund, pg_catalog;
1212
1213 --
1214 -- Name: p_list_zuord1; Type: INDEX; Schema: bund; Owner: -; Tablespace:
1215 --
1216
1217 CREATE INDEX p_list_zuord1 ON list_zuordnung USING btree (list_id);
1218
1219
1220 --
1221 -- Name: p_list_zuord2; Type: INDEX; Schema: bund; Owner: -; Tablespace:
1222 --
1223
1224 CREATE INDEX p_list_zuord2 ON list_zuordnung USING hash (list_id);
1225
1226
1227 SET search_path = land, pg_catalog;
1228
1229 --
1230 -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1231 --
1232
1233 CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id);
1234
1235
1236 --
1237 -- Name: messung_translation_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1238 --
1239
1240 CREATE INDEX messung_translation_messungs_id_idx ON messung_translation USING btree (messungs_id);
1241
1242
1243 --
1244 -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1245 --
1246
1247 CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id);
1248
1249
1250 --
1251 -- Name: probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1252 --
1253
1254 CREATE INDEX probe_id_idx ON probe USING btree (id);
1255
1256
1257 --
1258 -- Name: probe_translation_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1259 --
1260
1261 CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id);
1262
1263
1264 SET search_path = bund, pg_catalog;
1265
1266 --
1267 -- Name: tree_timestamp_status; Type: TRIGGER; Schema: bund; Owner: -
1268 --
1269
1270 CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_time_status();
1271
1272
1273 SET search_path = land, pg_catalog;
1274
1275 --
1276 -- Name: tree_timestamp_messung; Type: TRIGGER; Schema: land; Owner: -
1277 --
1278
1279 CREATE TRIGGER tree_timestamp_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_time_messung();
1280
1281
1282 --
1283 -- Name: tree_timestamp_messwert; Type: TRIGGER; Schema: land; Owner: -
1284 --
1285
1286 CREATE TRIGGER tree_timestamp_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_time_messwert();
1287
1288
1289 --
1290 -- Name: tree_timestamp_ort; Type: TRIGGER; Schema: land; Owner: -
1291 --
1292
1293 CREATE TRIGGER tree_timestamp_ort BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_time_ort();
1294
1295
1296 --
1297 -- Name: tree_timestamp_probe; Type: TRIGGER; Schema: land; Owner: -
1298 --
1299
1300 CREATE TRIGGER tree_timestamp_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_time_probe();
1301
1302
1303 --
1304 -- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: -
1305 --
1306
1307 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert();
1308
1309
1310 SET search_path = bund, pg_catalog;
1311
1312 --
1313 -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1314 --
1315
1316 ALTER TABLE ONLY kommentar_m
1317 ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id);
1318
1319
1320 --
1321 -- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1322 --
1323
1324 ALTER TABLE ONLY kommentar_p
1325 ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
1326
1327
1328 --
1329 -- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1330 --
1331
1332 ALTER TABLE ONLY messung
1333 ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id);
1334
1335
1336 --
1337 -- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1338 --
1339
1340 ALTER TABLE ONLY messung
1341 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
1342
1343
1344 --
1345 -- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1346 --
1347 344
1348 ALTER TABLE ONLY messung 345 ALTER TABLE ONLY messung
1349 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id); 346 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
1350 347
1351 348
1352 -- 349 --
1353 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 350 -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1354 -- 351 --
1355 352
1356 ALTER TABLE ONLY messwert 353 CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id);
1357 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id); 354
1358 355
1359 356 --
1360 -- 357 -- Name: ort_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1361 -- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 358 --
1362 -- 359
1363 360 CREATE INDEX ort_probe_id_idx ON ortszuordnung USING btree (probe_id);
1364 ALTER TABLE ONLY messwert 361
1365 ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id); 362
1366 363 --
1367 364 -- Name: zusatz_wert_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1368 -- 365 --
1369 -- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 366
1370 -- 367 CREATE INDEX zusatz_wert_probe_id_idx ON zusatz_wert USING btree (probe_id);
1371 368
1372 ALTER TABLE ONLY messwert 369
1373 ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id); 370 --
1374 371 -- Name: kommentar_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1375 372 --
1376 -- 373
1377 -- Name: ortszuordnung_ort_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 374 CREATE INDEX kommentar_probe_id_idx ON kommentar_p USING btree (probe_id);
1378 -- 375
1379 376
1380 ALTER TABLE ONLY ortszuordnung 377 --
1381 ADD CONSTRAINT ortszuordnung_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id); 378 -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1382 379 --
1383 380
1384 -- 381 CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id);
1385 -- Name: ortszuordnung_ortszuordnung_typ_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 382
1386 -- 383
1387 384 --
1388 ALTER TABLE ONLY ortszuordnung 385 -- Name: status_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1389 ADD CONSTRAINT ortszuordnung_ortszuordnung_typ_fkey FOREIGN KEY (ortszuordnung_typ) REFERENCES stammdaten.ortszuordnung_typ(id); 386 --
1390 387
1391 388 CREATE INDEX status_messungs_id_idx ON status_protokoll USING btree (messungs_id);
1392 -- 389
1393 -- Name: ortszuordnung_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 390
1394 -- 391 --
1395 392 -- Name: kommentar_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
1396 ALTER TABLE ONLY ortszuordnung 393 --
1397 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); 394
1398 395 CREATE INDEX kommentar_messungs_id_idx ON kommentar_m USING btree (messungs_id);
1399 396
1400 -- 397
1401 -- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 398 --
1402 -- 399 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: -
1403 400 --
1404 ALTER TABLE ONLY status_protokoll 401
1405 ADD CONSTRAINT status_protokoll_status_stufe_fkey FOREIGN KEY (status_stufe) REFERENCES stammdaten.status_stufe(id); 402 COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
1406 403
1407 404
1408 -- 405 --
1409 -- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 406 -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: -
1410 -- 407 --
1411 408
1412 ALTER TABLE ONLY status_protokoll 409 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel';
1413 ADD CONSTRAINT status_protokoll_status_wert_fkey FOREIGN KEY (status_wert) REFERENCES stammdaten.status_wert(id); 410
1414 411
1415 412 --
1416 -- 413 -- Name: COLUMN probe.test; Type: COMMENT; Schema: land; Owner: -
1417 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 414 --
1418 -- 415
1419 416 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
1420 ALTER TABLE ONLY zusatz_wert 417
1421 ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); 418
1422 419 --
1423 420 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: land; Owner: -
1424 -- 421 --
1425 -- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 422
1426 -- 423 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
1427 424
1428 ALTER TABLE ONLY zusatz_wert 425
1429 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id); 426 --
1430 427 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: land; Owner: -
1431 428 --
1432 SET search_path = land, pg_catalog; 429
1433 430 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
1434 -- 431
1435 -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 432
1436 -- 433 --
1437 434 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: land; Owner: -
1438 ALTER TABLE ONLY kommentar_m 435 --
1439 ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; 436
1440 437 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel';
1441 438
1442 -- 439
1443 -- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 440 --
1444 -- 441 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: land; Owner: -
1445 442 --
1446 ALTER TABLE ONLY kommentar_p 443
1447 ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; 444 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
1448 445
1449 446
1450 -- 447 --
1451 -- Name: messprogramm_mmt_messprogramm_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 448 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: land; Owner: -
1452 -- 449 --
1453 450
1454 ALTER TABLE ONLY messprogramm_mmt 451 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
1455 ADD CONSTRAINT messprogramm_mmt_messprogramm_id_fkey FOREIGN KEY (messprogramm_id) REFERENCES messprogramm(id) ON DELETE CASCADE; 452
1456 453
1457 454 --
1458 -- 455 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: land; Owner: -
1459 -- Name: messprogramm_mmt_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 456 --
1460 -- 457
1461 458 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
1462 ALTER TABLE ONLY messprogramm_mmt 459
1463 ADD CONSTRAINT messprogramm_mmt_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id); 460
1464 461 --
1465 462 -- Name: COLUMN probe.media; Type: COMMENT; Schema: land; Owner: -
1466 -- 463 --
1467 -- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 464
1468 -- 465 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
1469 466
1470 ALTER TABLE ONLY messung 467
1471 ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id) ON DELETE CASCADE; 468 --
1472 469 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: land; Owner: -
1473 470 --
1474 -- 471
1475 -- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 472 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
1476 -- 473
1477 474
1478 ALTER TABLE ONLY messung 475 --
1479 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; 476 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
1480 477 --
1481 478
1482 -- 479 COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
1483 -- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1484 --
1485
1486 ALTER TABLE ONLY messung
1487 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
1488
1489
1490 --
1491 -- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1492 --
1493
1494 ALTER TABLE ONLY messung_translation
1495 ADD CONSTRAINT messung_translation_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
1496
1497
1498 --
1499 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1500 --
1501
1502 ALTER TABLE ONLY messwert
1503 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
1504
1505
1506 --
1507 -- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1508 --
1509
1510 ALTER TABLE ONLY messwert
1511 ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id);
1512
1513
1514 --
1515 -- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1516 --
1517
1518 ALTER TABLE ONLY messwert
1519 ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
1520
1521
1522 --
1523 -- Name: ortszuordnung_ort_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1524 --
1525
1526 ALTER TABLE ONLY ortszuordnung
1527 ADD CONSTRAINT ortszuordnung_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
1528
1529
1530 --
1531 -- Name: ortszuordnung_ortszuordnung_typ_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1532 --
1533
1534 ALTER TABLE ONLY ortszuordnung
1535 ADD CONSTRAINT ortszuordnung_ortszuordnung_typ_fkey FOREIGN KEY (ortszuordnung_typ) REFERENCES stammdaten.ortszuordnung_typ(id);
1536
1537
1538 --
1539 -- Name: ortszuordnung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1540 --
1541
1542 ALTER TABLE ONLY ortszuordnung
1543 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
1544
1545
1546 --
1547 -- Name: probe_translation_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1548 --
1549
1550 ALTER TABLE ONLY probe_translation
1551 ADD CONSTRAINT probe_translation_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
1552
1553
1554 --
1555 -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1556 --
1557
1558 ALTER TABLE ONLY status_protokoll
1559 ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
1560
1561
1562 --
1563 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1564 --
1565
1566 ALTER TABLE ONLY zusatz_wert
1567 ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
1568
1569
1570 --
1571 -- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1572 --
1573
1574 ALTER TABLE ONLY zusatz_wert
1575 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id);
1576 480
1577 481
1578 COMMIT; 482 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)