Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 751:37a8f4d157c7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Updated lada schema: Added status workflow model and cleaned up some triggers.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 30 Oct 2015 15:06:22 +0100
parents c998673c6d1e
children f2ae2d734b80
comparison
equal deleted inserted replaced
747:214508fd95e7 751:37a8f4d157c7
59 59
60 60
61 SET search_path = bund, pg_catalog; 61 SET search_path = bund, pg_catalog;
62 62
63 -- 63 --
64 -- Name: is_kommentar_unique(); Type: FUNCTION; Schema: bund; Owner: - 64 -- Name: update_time_status(); Type: FUNCTION; Schema: bund; Owner: -
65 -- 65 --
66 66
67 CREATE FUNCTION is_kommentar_unique() RETURNS trigger 67 CREATE FUNCTION update_time_status() RETURNS trigger
68 LANGUAGE plpgsql 68 LANGUAGE plpgsql
69 AS $$ 69 AS $$
70 BEGIN 70 BEGIN
71 -- Check that empname and salary are given 71 NEW.tree_modified = now();
72 IF NEW.id IN (SELECT id from bund.kommentar) THEN 72 RETURN NEW;
73 RAISE EXCEPTION 'Key (id)=(%) already present in bund.messung', NEW.id;
74 ELSE
75 RETURN NEW;
76 END IF;
77 END;
78 $$;
79
80
81 --
82 -- Name: is_messung_unique(); Type: FUNCTION; Schema: bund; Owner: -
83 --
84
85 CREATE FUNCTION is_messung_unique() RETURNS trigger
86 LANGUAGE plpgsql
87 AS $$
88 BEGIN
89 -- Check that empname and salary are given
90 IF NEW.id IN (SELECT id from bund.messung) THEN
91 RAISE EXCEPTION 'Key (id)=(%) already present in bund.messung', NEW.id;
92 ELSE
93 RETURN NEW;
94 END IF;
95 END;
96 $$;
97
98
99 --
100 -- Name: is_messwert_unique(); Type: FUNCTION; Schema: bund; Owner: -
101 --
102
103 CREATE FUNCTION is_messwert_unique() RETURNS trigger
104 LANGUAGE plpgsql
105 AS $$
106 BEGIN
107 -- Check that empname and salary are given
108 IF NEW.id IN (SELECT id from bund.messwert) THEN
109 RAISE EXCEPTION 'Key (id)=(%) already present in bund.messwert', NEW.id;
110 ELSE
111 RETURN NEW;
112 END IF;
113 END;
114 $$;
115
116
117 --
118 -- Name: is_ort_unique(); Type: FUNCTION; Schema: bund; Owner: -
119 --
120
121 CREATE FUNCTION is_ort_unique() RETURNS trigger
122 LANGUAGE plpgsql
123 AS $$
124 BEGIN
125 -- Check that empname and salary are given
126 IF NEW.id IN (SELECT id from bund.ort) THEN
127 RAISE EXCEPTION 'Key (id)=(%) already present in bund.ort', NEW.id;
128 ELSE
129 RETURN NEW;
130 END IF;
131 END;
132 $$;
133
134
135 --
136 -- Name: is_probe_unique(); Type: FUNCTION; Schema: bund; Owner: -
137 --
138
139 CREATE FUNCTION is_probe_unique() RETURNS trigger
140 LANGUAGE plpgsql
141 AS $$
142 BEGIN
143 -- Check that empname and salary are given
144 IF NEW.id IN (SELECT id from bund.probe) THEN
145 RAISE EXCEPTION 'Key (id)=(%) already present in bund.probe', NEW.id;
146 ELSE
147 RETURN NEW;
148 END IF;
149 END;
150 $$;
151
152
153 --
154 -- Name: is_status_unique(); Type: FUNCTION; Schema: bund; Owner: -
155 --
156
157 CREATE FUNCTION is_status_unique() RETURNS trigger
158 LANGUAGE plpgsql
159 AS $$
160 BEGIN
161 -- Check that empname and salary are given
162 IF NEW.id IN (SELECT id from bund.status) THEN
163 RAISE EXCEPTION 'Key (id)=(%) already present in bund.status', NEW.id;
164 ELSE
165 RETURN NEW;
166 END IF;
167 END;
168 $$;
169
170
171 --
172 -- Name: is_zusatz_wert_unique(); Type: FUNCTION; Schema: bund; Owner: -
173 --
174
175 CREATE FUNCTION is_zusatz_wert_unique() RETURNS trigger
176 LANGUAGE plpgsql
177 AS $$
178 BEGIN
179 -- Check that empname and salary are given
180 IF NEW.id IN (SELECT id from bund.zusatz_wert) THEN
181 RAISE EXCEPTION 'Key (id)=(%) already present in bund.zusatz_wert', NEW.id;
182 ELSE
183 RETURN NEW;
184 END IF;
185 END; 73 END;
186 $$; 74 $$;
187 75
188 76
189 SET search_path = land, pg_catalog; 77 SET search_path = land, pg_catalog;
212 AS $$ 100 AS $$
213 BEGIN 101 BEGIN
214 RAISE NOTICE 'messung is %',NEW.id; 102 RAISE NOTICE 'messung is %',NEW.id;
215 NEW.tree_modified = now(); 103 NEW.tree_modified = now();
216 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id; 104 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
217 UPDATE land.status SET tree_modified = now() WHERE messungs_id = NEW.id; 105 UPDATE bund.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id;
218 RETURN NEW; 106 RETURN NEW;
219 END; 107 END;
220 $$; 108 $$;
221 109
222 110
260 NEW.tree_modified = now(); 148 NEW.tree_modified = now();
261 RAISE NOTICE 'updating other rows'; 149 RAISE NOTICE 'updating other rows';
262 UPDATE land.messung SET tree_modified = now() WHERE probe_id = NEW.id; 150 UPDATE land.messung SET tree_modified = now() WHERE probe_id = NEW.id;
263 UPDATE land.ort SET tree_modified = now() WHERE probe_id = NEW.id; 151 UPDATE land.ort SET tree_modified = now() WHERE probe_id = NEW.id;
264 UPDATE land.zusatz_wert SET tree_modified = now() WHERE probe_id = NEW.id; 152 UPDATE land.zusatz_wert SET tree_modified = now() WHERE probe_id = NEW.id;
265 RETURN NEW;
266 END;
267 $$;
268
269
270 --
271 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
272 --
273
274 CREATE FUNCTION update_time_status() RETURNS trigger
275 LANGUAGE plpgsql
276 AS $$
277 BEGIN
278 NEW.tree_modified = now();
279 RETURN NEW; 153 RETURN NEW;
280 END; 154 END;
281 $$; 155 $$;
282 156
283 157
478 nebenproben_nr character varying(10), 352 nebenproben_nr character varying(10),
479 mmt_id character varying(2) NOT NULL, 353 mmt_id character varying(2) NOT NULL,
480 messdauer integer, 354 messdauer integer,
481 messzeitpunkt timestamp with time zone, 355 messzeitpunkt timestamp with time zone,
482 fertig boolean DEFAULT false NOT NULL, 356 fertig boolean DEFAULT false NOT NULL,
357 status integer,
483 letzte_aenderung timestamp without time zone DEFAULT now() 358 letzte_aenderung timestamp without time zone DEFAULT now()
484 ); 359 );
485 360
486 361
487 -- 362 --
590 CREATE TABLE probe ( 465 CREATE TABLE probe (
591 id integer DEFAULT nextval('probe_id_seq'::regclass) NOT NULL, 466 id integer DEFAULT nextval('probe_id_seq'::regclass) NOT NULL,
592 test boolean DEFAULT false NOT NULL, 467 test boolean DEFAULT false NOT NULL,
593 netzbetreiber_id character varying(2), 468 netzbetreiber_id character varying(2),
594 mst_id character varying(5), 469 mst_id character varying(5),
470 labor_mst_id character varying(5),
595 hauptproben_nr character varying(20), 471 hauptproben_nr character varying(20),
596 datenbasis_id smallint, 472 datenbasis_id smallint,
597 ba_id character varying(1), 473 ba_id character varying(1),
598 probenart_id smallint NOT NULL, 474 probenart_id smallint NOT NULL,
599 media_desk character varying(100), 475 media_desk character varying(100),
622 498
623 -- 499 --
624 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: bund; Owner: - 500 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: bund; Owner: -
625 -- 501 --
626 502
627 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle/Messlabor'; 503 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
504
505
506 --
507 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: bund; Owner: -
508 --
509
510 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
628 511
629 512
630 -- 513 --
631 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: bund; Owner: - 514 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: bund; Owner: -
632 -- 515 --
680 NO MAXVALUE 563 NO MAXVALUE
681 CACHE 1; 564 CACHE 1;
682 565
683 566
684 -- 567 --
685 -- Name: status_id_seq; Type: SEQUENCE; Schema: bund; Owner: - 568 -- Name: status_protokoll; Type: TABLE; Schema: bund; Owner: -; Tablespace:
686 -- 569 --
687 570
688 CREATE SEQUENCE status_id_seq 571 CREATE TABLE status_protokoll (
689 START WITH 1 572 status_stufe integer NOT NULL,
690 INCREMENT BY 1 573 status_wert integer NOT NULL,
691 NO MINVALUE 574 tree_modified timestamp without time zone DEFAULT now()
692 NO MAXVALUE 575 )
693 CACHE 1; 576 INHERITS (kommentar_m);
694
695
696 --
697 -- Name: status; Type: TABLE; Schema: bund; Owner: -; Tablespace:
698 --
699
700 CREATE TABLE status (
701 id integer DEFAULT nextval('status_id_seq'::regclass) NOT NULL,
702 messungs_id integer NOT NULL,
703 erzeuger character varying(5) NOT NULL,
704 status smallint,
705 s_datum timestamp with time zone NOT NULL,
706 s_kommentar character varying(1024)
707 );
708 577
709 578
710 -- 579 --
711 -- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: bund; Owner: - 580 -- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
712 -- 581 --
948 817
949 ALTER SEQUENCE probe_translation_id_seq OWNED BY probe_translation.id; 818 ALTER SEQUENCE probe_translation_id_seq OWNED BY probe_translation.id;
950 819
951 820
952 -- 821 --
953 -- Name: status; Type: TABLE; Schema: land; Owner: -; Tablespace: 822 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
954 -- 823 --
955 824
956 CREATE TABLE status ( 825 CREATE TABLE status_protokoll (
957 tree_modified timestamp without time zone DEFAULT now()
958 ) 826 )
959 INHERITS (bund.status); 827 INHERITS (bund.status_protokoll);
960 828
961 829
962 -- 830 --
963 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace: 831 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
964 -- 832 --
1813 1681
1814 ALTER SEQUENCE staat_id_seq OWNED BY staat.id; 1682 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
1815 1683
1816 1684
1817 -- 1685 --
1686 -- Name: status_stufe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1687 --
1688
1689 CREATE TABLE status_stufe (
1690 id integer NOT NULL,
1691 stufe character varying(50)
1692 );
1693
1694
1695 --
1696 -- Name: status_wert; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1697 --
1698
1699 CREATE TABLE status_wert (
1700 id integer NOT NULL,
1701 wert character varying(50)
1702 );
1703
1704
1705 --
1818 -- Name: umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 1706 -- Name: umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1819 -- 1707 --
1820 1708
1821 CREATE TABLE umwelt ( 1709 CREATE TABLE umwelt (
1822 id character varying(3) NOT NULL, 1710 id character varying(3) NOT NULL,
1885 -- 1773 --
1886 1774
1887 ALTER TABLE ONLY list ALTER COLUMN id SET DEFAULT nextval('list_id_seq'::regclass); 1775 ALTER TABLE ONLY list ALTER COLUMN id SET DEFAULT nextval('list_id_seq'::regclass);
1888 1776
1889 1777
1778 --
1779 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
1780 --
1781
1782 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
1783
1784
1785 --
1786 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
1787 --
1788
1789 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now();
1790
1791
1890 SET search_path = land, pg_catalog; 1792 SET search_path = land, pg_catalog;
1891 1793
1892 -- 1794 --
1893 -- Name: id; Type: DEFAULT; Schema: land; Owner: - 1795 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1894 -- 1796 --
2024 1926
2025 -- 1927 --
2026 -- Name: id; Type: DEFAULT; Schema: land; Owner: - 1928 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
2027 -- 1929 --
2028 1930
2029 ALTER TABLE ONLY status ALTER COLUMN id SET DEFAULT nextval('bund.status_id_seq'::regclass); 1931 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
1932
1933
1934 --
1935 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
1936 --
1937
1938 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now();
1939
1940
1941 --
1942 -- Name: tree_modified; Type: DEFAULT; Schema: land; Owner: -
1943 --
1944
1945 ALTER TABLE ONLY status_protokoll ALTER COLUMN tree_modified SET DEFAULT now();
2030 1946
2031 1947
2032 -- 1948 --
2033 -- Name: id; Type: DEFAULT; Schema: land; Owner: - 1949 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
2034 -- 1950 --
2186 ALTER TABLE ONLY probe 2102 ALTER TABLE ONLY probe
2187 ADD CONSTRAINT probe_pkey PRIMARY KEY (id); 2103 ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
2188 2104
2189 2105
2190 -- 2106 --
2191 -- Name: status_messungs_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 2107 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2192 -- 2108 --
2193 2109
2194 ALTER TABLE ONLY status 2110 ALTER TABLE ONLY status_protokoll
2195 ADD CONSTRAINT status_messungs_id_key UNIQUE (messungs_id); 2111 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
2196
2197
2198 --
2199 -- Name: status_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2200 --
2201
2202 ALTER TABLE ONLY status
2203 ADD CONSTRAINT status_pkey PRIMARY KEY (id);
2204 2112
2205 2113
2206 -- 2114 --
2207 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 2115 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2208 -- 2116 --
2324 ALTER TABLE ONLY probe_translation 2232 ALTER TABLE ONLY probe_translation
2325 ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id); 2233 ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id);
2326 2234
2327 2235
2328 -- 2236 --
2329 -- Name: status_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 2237 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2330 -- 2238 --
2331 2239
2332 ALTER TABLE ONLY status 2240 ALTER TABLE ONLY status_protokoll
2333 ADD CONSTRAINT status_messungs_id_key UNIQUE (messungs_id); 2241 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
2334
2335
2336 --
2337 -- Name: status_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2338 --
2339
2340 ALTER TABLE ONLY status
2341 ADD CONSTRAINT status_pkey PRIMARY KEY (id);
2342 2242
2343 2243
2344 -- 2244 --
2345 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 2245 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2346 -- 2246 --
2518 ALTER TABLE ONLY staat 2418 ALTER TABLE ONLY staat
2519 ADD CONSTRAINT staat_pkey PRIMARY KEY (id); 2419 ADD CONSTRAINT staat_pkey PRIMARY KEY (id);
2520 2420
2521 2421
2522 -- 2422 --
2423 -- Name: status_stufe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2424 --
2425
2426 ALTER TABLE ONLY status_stufe
2427 ADD CONSTRAINT status_stufe_pkey PRIMARY KEY (id);
2428
2429
2430 --
2431 -- Name: status_wert_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2432 --
2433
2434 ALTER TABLE ONLY status_wert
2435 ADD CONSTRAINT status_wert_pkey PRIMARY KEY (id);
2436
2437
2438 --
2523 -- Name: umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 2439 -- Name: umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2524 -- 2440 --
2525 2441
2526 ALTER TABLE ONLY umwelt 2442 ALTER TABLE ONLY umwelt
2527 ADD CONSTRAINT umwelt_pkey PRIMARY KEY (id); 2443 ADD CONSTRAINT umwelt_pkey PRIMARY KEY (id);
2610 -- 2526 --
2611 2527
2612 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); 2528 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
2613 2529
2614 2530
2531 SET search_path = bund, pg_catalog;
2532
2533 --
2534 -- Name: tree_timestamp_status; Type: TRIGGER; Schema: bund; Owner: -
2535 --
2536
2537 CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_time_status();
2538
2539
2615 SET search_path = land, pg_catalog; 2540 SET search_path = land, pg_catalog;
2616 2541
2617 -- 2542 --
2618 -- Name: tree_timestamp_messung; Type: TRIGGER; Schema: land; Owner: - 2543 -- Name: tree_timestamp_messung; Type: TRIGGER; Schema: land; Owner: -
2619 -- 2544 --
2641 2566
2642 CREATE TRIGGER tree_timestamp_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_time_probe(); 2567 CREATE TRIGGER tree_timestamp_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_time_probe();
2643 2568
2644 2569
2645 -- 2570 --
2646 -- Name: tree_timestamp_status; Type: TRIGGER; Schema: land; Owner: -
2647 --
2648
2649 CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status FOR EACH ROW EXECUTE PROCEDURE update_time_status();
2650
2651
2652 --
2653 -- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: - 2571 -- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: -
2654 -- 2572 --
2655 2573
2656 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert(); 2574 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert();
2657
2658
2659 --
2660 -- Name: verify_kommentar_m_id; Type: TRIGGER; Schema: land; Owner: -
2661 --
2662
2663 CREATE TRIGGER verify_kommentar_m_id BEFORE INSERT ON kommentar_m FOR EACH ROW EXECUTE PROCEDURE bund.is_kommentar_unique();
2664
2665
2666 --
2667 -- Name: verify_kommentar_p_id; Type: TRIGGER; Schema: land; Owner: -
2668 --
2669
2670 CREATE TRIGGER verify_kommentar_p_id BEFORE INSERT ON kommentar_p FOR EACH ROW EXECUTE PROCEDURE bund.is_kommentar_unique();
2671
2672
2673 --
2674 -- Name: verify_messung_id; Type: TRIGGER; Schema: land; Owner: -
2675 --
2676
2677 CREATE TRIGGER verify_messung_id BEFORE INSERT ON messung FOR EACH ROW EXECUTE PROCEDURE bund.is_messung_unique();
2678
2679
2680 --
2681 -- Name: verify_messwert_id; Type: TRIGGER; Schema: land; Owner: -
2682 --
2683
2684 CREATE TRIGGER verify_messwert_id BEFORE INSERT ON messwert FOR EACH ROW EXECUTE PROCEDURE bund.is_messwert_unique();
2685
2686
2687 --
2688 -- Name: verify_ort_id; Type: TRIGGER; Schema: land; Owner: -
2689 --
2690
2691 CREATE TRIGGER verify_ort_id BEFORE INSERT ON ort FOR EACH ROW EXECUTE PROCEDURE bund.is_ort_unique();
2692
2693
2694 --
2695 -- Name: verify_probe_id; Type: TRIGGER; Schema: land; Owner: -
2696 --
2697
2698 CREATE TRIGGER verify_probe_id BEFORE INSERT ON probe FOR EACH ROW EXECUTE PROCEDURE bund.is_probe_unique();
2699
2700
2701 --
2702 -- Name: verify_status_id; Type: TRIGGER; Schema: land; Owner: -
2703 --
2704
2705 CREATE TRIGGER verify_status_id BEFORE INSERT ON status FOR EACH ROW EXECUTE PROCEDURE bund.is_status_unique();
2706
2707
2708 --
2709 -- Name: verify_zusatz_wert_id; Type: TRIGGER; Schema: land; Owner: -
2710 --
2711
2712 CREATE TRIGGER verify_zusatz_wert_id BEFORE INSERT ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE bund.is_zusatz_wert_unique();
2713 2575
2714 2576
2715 SET search_path = bund, pg_catalog; 2577 SET search_path = bund, pg_catalog;
2716 2578
2717 -- 2579 --
2745 ALTER TABLE ONLY messung 2607 ALTER TABLE ONLY messung
2746 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); 2608 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
2747 2609
2748 2610
2749 -- 2611 --
2612 -- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2613 --
2614
2615 ALTER TABLE ONLY messung
2616 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
2617
2618
2619 --
2750 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 2620 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2751 -- 2621 --
2752 2622
2753 ALTER TABLE ONLY messwert 2623 ALTER TABLE ONLY messwert
2754 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id); 2624 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
2793 ALTER TABLE ONLY probe 2663 ALTER TABLE ONLY probe
2794 ADD CONSTRAINT probe_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id); 2664 ADD CONSTRAINT probe_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
2795 2665
2796 2666
2797 -- 2667 --
2668 -- Name: probe_labor_mst_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2669 --
2670
2671 ALTER TABLE ONLY probe
2672 ADD CONSTRAINT probe_labor_mst_id_fkey FOREIGN KEY (labor_mst_id) REFERENCES stammdaten.mess_stelle(id);
2673
2674
2675 --
2798 -- Name: probe_mst_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 2676 -- Name: probe_mst_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2799 -- 2677 --
2800 2678
2801 ALTER TABLE ONLY probe 2679 ALTER TABLE ONLY probe
2802 ADD CONSTRAINT probe_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id); 2680 ADD CONSTRAINT probe_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
2803 2681
2804 2682
2805 -- 2683 --
2806 -- Name: probe_mst_id_fkey1; Type: FK CONSTRAINT; Schema: bund; Owner: -
2807 --
2808
2809 ALTER TABLE ONLY probe
2810 ADD CONSTRAINT probe_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
2811
2812
2813 --
2814 -- Name: probe_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 2684 -- Name: probe_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2815 -- 2685 --
2816 2686
2817 ALTER TABLE ONLY probe 2687 ALTER TABLE ONLY probe
2818 ADD CONSTRAINT probe_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id); 2688 ADD CONSTRAINT probe_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
2833 ALTER TABLE ONLY probe 2703 ALTER TABLE ONLY probe
2834 ADD CONSTRAINT probe_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id); 2704 ADD CONSTRAINT probe_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
2835 2705
2836 2706
2837 -- 2707 --
2838 -- Name: status_erzeuger_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 2708 -- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2839 -- 2709 --
2840 2710
2841 ALTER TABLE ONLY status 2711 ALTER TABLE ONLY status_protokoll
2842 ADD CONSTRAINT status_erzeuger_fkey FOREIGN KEY (erzeuger) REFERENCES stammdaten.mess_stelle(id); 2712 ADD CONSTRAINT status_protokoll_status_stufe_fkey FOREIGN KEY (status_stufe) REFERENCES stammdaten.status_stufe(id);
2843 2713
2844 2714
2845 -- 2715 --
2846 -- Name: status_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 2716 -- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2847 -- 2717 --
2848 2718
2849 ALTER TABLE ONLY status 2719 ALTER TABLE ONLY status_protokoll
2850 ADD CONSTRAINT status_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id); 2720 ADD CONSTRAINT status_protokoll_status_wert_fkey FOREIGN KEY (status_wert) REFERENCES stammdaten.status_wert(id);
2851 2721
2852 2722
2853 -- 2723 --
2854 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 2724 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2855 -- 2725 --
2963 ALTER TABLE ONLY messung 2833 ALTER TABLE ONLY messung
2964 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; 2834 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
2965 2835
2966 2836
2967 -- 2837 --
2838 -- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2839 --
2840
2841 ALTER TABLE ONLY messung
2842 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
2843
2844
2845 --
2968 -- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 2846 -- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2969 -- 2847 --
2970 2848
2971 ALTER TABLE ONLY messung_translation 2849 ALTER TABLE ONLY messung_translation
2972 ADD CONSTRAINT messung_translation_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; 2850 ADD CONSTRAINT messung_translation_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
3059 ALTER TABLE ONLY probe 2937 ALTER TABLE ONLY probe
3060 ADD CONSTRAINT probe_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id); 2938 ADD CONSTRAINT probe_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
3061 2939
3062 2940
3063 -- 2941 --
3064 -- Name: status_erzeuger_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 2942 -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3065 -- 2943 --
3066 2944
3067 ALTER TABLE ONLY status 2945 ALTER TABLE ONLY status_protokoll
3068 ADD CONSTRAINT status_erzeuger_fkey FOREIGN KEY (erzeuger) REFERENCES stammdaten.mess_stelle(id); 2946 ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
3069
3070
3071 --
3072 -- Name: status_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3073 --
3074
3075 ALTER TABLE ONLY status
3076 ADD CONSTRAINT status_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
3077 2947
3078 2948
3079 -- 2949 --
3080 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 2950 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3081 -- 2951 --
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)