Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 1000:1489f0ade850 schema-update

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Moved FK constraints into table definition.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 01 Jul 2016 19:01:32 +0200
parents 813461e9a7be
children 1c4aa7ea63aa
comparison
equal deleted inserted replaced
999:813461e9a7be 1000:1489f0ade850
207 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace: 207 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
208 -- 208 --
209 209
210 CREATE TABLE messprogramm_mmt ( 210 CREATE TABLE messprogramm_mmt (
211 id serial PRIMARY KEY, 211 id serial PRIMARY KEY,
212 messprogramm_id integer NOT NULL, 212 messprogramm_id integer NOT NULL REFERENCES messprogramm ON DELETE CASCADE,
213 mmt_id character varying(2) NOT NULL, 213 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode,
214 messgroessen integer[], 214 messgroessen integer[],
215 letzte_aenderung timestamp without time zone DEFAULT now() 215 letzte_aenderung timestamp without time zone DEFAULT now()
216 ); 216 );
217 CREATE TRIGGER letzte_aenderung_messprogramm_mmt BEFORE UPDATE ON messprogramm_mmt FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 217 CREATE TRIGGER letzte_aenderung_messprogramm_mmt BEFORE UPDATE ON messprogramm_mmt FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
218 218
261 CREATE TABLE kommentar_p ( 261 CREATE TABLE kommentar_p (
262 id serial PRIMARY KEY, 262 id serial PRIMARY KEY,
263 erzeuger character varying(5) NOT NULL, 263 erzeuger character varying(5) NOT NULL,
264 datum timestamp without time zone DEFAULT now(), 264 datum timestamp without time zone DEFAULT now(),
265 text character varying(1024), 265 text character varying(1024),
266 probe_id integer NOT NULL 266 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE
267 ); 267 );
268 268
269 269
270 -- 270 --
271 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace: 271 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
272 -- 272 --
273 273
274 CREATE TABLE ortszuordnung ( 274 CREATE TABLE ortszuordnung (
275 id serial PRIMARY KEY, 275 id serial PRIMARY KEY,
276 probe_id integer NOT NULL, 276 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
277 ort_id bigint NOT NULL, 277 ort_id bigint NOT NULL REFERENCES stammdaten.ort,
278 ortszuordnung_typ character varying(1), 278 ortszuordnung_typ character varying(1) REFERENCES stammdaten.ortszuordnung_typ,
279 ortszusatztext character varying(100), 279 ortszusatztext character varying(100),
280 letzte_aenderung timestamp without time zone DEFAULT now(), 280 letzte_aenderung timestamp without time zone DEFAULT now(),
281 tree_modified timestamp without time zone DEFAULT now() 281 tree_modified timestamp without time zone DEFAULT now()
282 ); 282 );
283 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 283 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
287 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace: 287 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
288 -- 288 --
289 289
290 CREATE TABLE zusatz_wert ( 290 CREATE TABLE zusatz_wert (
291 id serial PRIMARY KEY, 291 id serial PRIMARY KEY,
292 probe_id integer NOT NULL, 292 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
293 pzs_id character varying(3) NOT NULL, 293 pzs_id character varying(3) NOT NULL REFERENCES stammdaten.proben_zusatz,
294 messwert_pzs double precision, 294 messwert_pzs double precision,
295 messfehler real, 295 messfehler real,
296 letzte_aenderung timestamp without time zone DEFAULT now(), 296 letzte_aenderung timestamp without time zone DEFAULT now(),
297 nwg_zu_messwert double precision, 297 nwg_zu_messwert double precision,
298 tree_modified timestamp without time zone DEFAULT now(), 298 tree_modified timestamp without time zone DEFAULT now(),
383 -- 383 --
384 384
385 CREATE TABLE messung ( 385 CREATE TABLE messung (
386 id serial PRIMARY KEY, 386 id serial PRIMARY KEY,
387 id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL, 387 id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL,
388 probe_id integer NOT NULL, 388 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
389 nebenproben_nr character varying(10), 389 nebenproben_nr character varying(10),
390 mmt_id character varying(2) NOT NULL, 390 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode ON DELETE CASCADE,
391 messdauer integer, 391 messdauer integer,
392 messzeitpunkt timestamp with time zone, 392 messzeitpunkt timestamp with time zone,
393 fertig boolean DEFAULT false NOT NULL, 393 fertig boolean DEFAULT false NOT NULL,
394 status integer, 394 status integer REFERENCES status_protokoll,
395 letzte_aenderung timestamp without time zone DEFAULT now(), 395 letzte_aenderung timestamp without time zone DEFAULT now(),
396 geplant boolean DEFAULT false NOT NULL, 396 geplant boolean DEFAULT false NOT NULL,
397 tree_modified timestamp without time zone DEFAULT now() 397 tree_modified timestamp without time zone DEFAULT now()
398 ); 398 );
399 399
405 CREATE TABLE kommentar_m ( 405 CREATE TABLE kommentar_m (
406 id serial PRIMARY KEY, 406 id serial PRIMARY KEY,
407 erzeuger character varying(5) NOT NULL, 407 erzeuger character varying(5) NOT NULL,
408 datum timestamp without time zone DEFAULT now(), 408 datum timestamp without time zone DEFAULT now(),
409 text character varying(1024), 409 text character varying(1024),
410 messungs_id integer NOT NULL 410 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE
411 ); 411 );
412 412
413 413
414 -- 414 --
415 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace: 415 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
416 -- 416 --
417 417
418 CREATE TABLE messwert ( 418 CREATE TABLE messwert (
419 id serial PRIMARY KEY, 419 id serial PRIMARY KEY,
420 messungs_id integer NOT NULL, 420 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
421 messgroesse_id integer NOT NULL, 421 messgroesse_id integer NOT NULL REFERENCES stammdaten.messgroesse,
422 messwert_nwg character varying(1), 422 messwert_nwg character varying(1),
423 messwert double precision NOT NULL, 423 messwert double precision NOT NULL,
424 messfehler real, 424 messfehler real,
425 nwg_zu_messwert double precision, 425 nwg_zu_messwert double precision,
426 meh_id smallint NOT NULL, 426 meh_id smallint NOT NULL REFERENCES stammdaten.mess_einheit,
427 grenzwertueberschreitung boolean DEFAULT false, 427 grenzwertueberschreitung boolean DEFAULT false,
428 letzte_aenderung timestamp without time zone DEFAULT now(), 428 letzte_aenderung timestamp without time zone DEFAULT now(),
429 tree_modified timestamp without time zone DEFAULT now(), 429 tree_modified timestamp without time zone DEFAULT now(),
430 UNIQUE (messungs_id, messgroesse_id) 430 UNIQUE (messungs_id, messgroesse_id)
431 ); 431 );
439 CREATE TABLE status_protokoll ( 439 CREATE TABLE status_protokoll (
440 id serial PRIMARY KEY, 440 id serial PRIMARY KEY,
441 erzeuger character varying(5) NOT NULL, 441 erzeuger character varying(5) NOT NULL,
442 datum timestamp without time zone DEFAULT now(), 442 datum timestamp without time zone DEFAULT now(),
443 text character varying(1024), 443 text character varying(1024),
444 messungs_id integer NOT NULL, 444 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
445 status_stufe integer NOT NULL, 445 status_stufe integer NOT NULL REFERENCES stammdaten.status_stufe,
446 status_wert integer NOT NULL, 446 status_wert integer NOT NULL REFERENCES stammdaten.status_wert,
447 tree_modified timestamp without time zone DEFAULT now() 447 tree_modified timestamp without time zone DEFAULT now()
448 ); 448 );
449 449
450 450
451 -- 451 --
509 -- 509 --
510 510
511 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert(); 511 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert();
512 512
513 513
514 --
515 -- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
516 --
517
518 ALTER TABLE ONLY status_protokoll
519 ADD CONSTRAINT status_protokoll_status_stufe_fkey FOREIGN KEY (status_stufe) REFERENCES stammdaten.status_stufe(id);
520
521
522 --
523 -- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
524 --
525
526 ALTER TABLE ONLY status_protokoll
527 ADD CONSTRAINT status_protokoll_status_wert_fkey FOREIGN KEY (status_wert) REFERENCES stammdaten.status_wert(id);
528
529
530 --
531 -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
532 --
533
534 ALTER TABLE ONLY kommentar_m
535 ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
536
537
538 --
539 -- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
540 --
541
542 ALTER TABLE ONLY kommentar_p
543 ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
544
545
546 --
547 -- Name: messprogramm_mmt_messprogramm_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
548 --
549
550 ALTER TABLE ONLY messprogramm_mmt
551 ADD CONSTRAINT messprogramm_mmt_messprogramm_id_fkey FOREIGN KEY (messprogramm_id) REFERENCES messprogramm(id) ON DELETE CASCADE;
552
553
554 --
555 -- Name: messprogramm_mmt_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
556 --
557
558 ALTER TABLE ONLY messprogramm_mmt
559 ADD CONSTRAINT messprogramm_mmt_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id);
560
561
562 --
563 -- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
564 --
565
566 ALTER TABLE ONLY messung
567 ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id) ON DELETE CASCADE;
568
569
570 --
571 -- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
572 --
573
574 ALTER TABLE ONLY messung
575 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
576
577
578 --
579 -- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
580 --
581
582 ALTER TABLE ONLY messung
583 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
584
585
586 --
587 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
588 --
589
590 ALTER TABLE ONLY messwert
591 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
592
593
594 --
595 -- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
596 --
597
598 ALTER TABLE ONLY messwert
599 ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id);
600
601
602 --
603 -- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
604 --
605
606 ALTER TABLE ONLY messwert
607 ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
608
609
610 --
611 -- Name: ortszuordnung_ort_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
612 --
613
614 ALTER TABLE ONLY ortszuordnung
615 ADD CONSTRAINT ortszuordnung_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
616
617
618 --
619 -- Name: ortszuordnung_ortszuordnung_typ_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
620 --
621
622 ALTER TABLE ONLY ortszuordnung
623 ADD CONSTRAINT ortszuordnung_ortszuordnung_typ_fkey FOREIGN KEY (ortszuordnung_typ) REFERENCES stammdaten.ortszuordnung_typ(id);
624
625
626 --
627 -- Name: ortszuordnung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
628 --
629
630 ALTER TABLE ONLY ortszuordnung
631 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
632
633
634 --
635 -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
636 --
637
638 ALTER TABLE ONLY status_protokoll
639 ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
640
641
642 --
643 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
644 --
645
646 ALTER TABLE ONLY zusatz_wert
647 ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
648
649
650 --
651 -- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
652 --
653
654 ALTER TABLE ONLY zusatz_wert
655 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id);
656
657
658 COMMIT; 514 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)