Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 899:4bbfc9cff5d7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve readability of DDL for probe tables.
author Tom Gottfried <tom@intevation.de>
date Wed, 23 Mar 2016 12:47:16 +0100
parents 9293d37c65f7
children 26d57ae6dd2f
comparison
equal deleted inserted replaced
898:9293d37c65f7 899:4bbfc9cff5d7
359 -- 359 --
360 -- Name: probe; Type: TABLE; Schema: bund; Owner: -; Tablespace: 360 -- Name: probe; Type: TABLE; Schema: bund; Owner: -; Tablespace:
361 -- 361 --
362 362
363 CREATE TABLE probe ( 363 CREATE TABLE probe (
364 id integer DEFAULT nextval('probe_id_seq'::regclass) NOT NULL, 364 id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass),
365 test boolean DEFAULT false NOT NULL, 365 test boolean DEFAULT false NOT NULL,
366 netzbetreiber_id character varying(2), 366 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber,
367 mst_id character varying(5), 367 mst_id character varying(5) REFERENCES stammdaten.mess_stelle,
368 labor_mst_id character varying(5), 368 labor_mst_id character varying(5) REFERENCES stammdaten.mess_stelle,
369 hauptproben_nr character varying(20), 369 hauptproben_nr character varying(20),
370 datenbasis_id smallint, 370 datenbasis_id smallint REFERENCES stammdaten.datenbasis,
371 ba_id character varying(1), 371 ba_id character varying(1),
372 probenart_id smallint NOT NULL, 372 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
373 media_desk character varying(100), 373 media_desk character varying(100),
374 media character varying(100), 374 media character varying(100),
375 umw_id character varying(3), 375 umw_id character varying(3) REFERENCES stammdaten.umwelt,
376 probeentnahme_beginn timestamp with time zone, 376 probeentnahme_beginn timestamp with time zone,
377 probeentnahme_ende timestamp with time zone, 377 probeentnahme_ende timestamp with time zone,
378 mittelungsdauer bigint, 378 mittelungsdauer bigint,
379 letzte_aenderung timestamp without time zone DEFAULT now() 379 letzte_aenderung timestamp without time zone DEFAULT now()
380 ); 380 );
381
382 381
383 -- 382 --
384 -- Name: COLUMN probe.id; Type: COMMENT; Schema: bund; Owner: - 383 -- Name: COLUMN probe.id; Type: COMMENT; Schema: bund; Owner: -
385 -- 384 --
386 385
684 solldatum_ende timestamp without time zone, 683 solldatum_ende timestamp without time zone,
685 tree_modified timestamp without time zone DEFAULT now() 684 tree_modified timestamp without time zone DEFAULT now()
686 ) 685 )
687 INHERITS (bund.probe); 686 INHERITS (bund.probe);
688 687
688 ALTER TABLE ONLY probe
689 ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
690
691 ALTER TABLE ONLY probe
692 ADD CONSTRAINT probe_datenbasis_id_fkey
693 FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
694
695 ALTER TABLE ONLY probe
696 ADD CONSTRAINT probe_mst_id_fkey
697 FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
698
699 ALTER TABLE ONLY probe
700 ADD CONSTRAINT probe_labor_mst_id_fkey
701 FOREIGN KEY (labor_mst_id) REFERENCES stammdaten.mess_stelle(id);
702
703 ALTER TABLE ONLY probe
704 ADD CONSTRAINT probe_netzbetreiber_id_fkey
705 FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
706
707 ALTER TABLE ONLY probe
708 ADD CONSTRAINT probe_probenart_id_fkey
709 FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
710
711 ALTER TABLE ONLY probe
712 ADD CONSTRAINT probe_umw_id_fkey
713 FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
714
689 715
690 -- 716 --
691 -- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace: 717 -- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace:
692 -- 718 --
693 719
896 922
897 -- 923 --
898 -- Name: id; Type: DEFAULT; Schema: land; Owner: - 924 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
899 -- 925 --
900 926
901 ALTER TABLE ONLY probe ALTER COLUMN id SET DEFAULT nextval('bund.probe_id_seq'::regclass);
902
903
904 --
905 -- Name: test; Type: DEFAULT; Schema: land; Owner: -
906 --
907
908 ALTER TABLE ONLY probe ALTER COLUMN test SET DEFAULT false;
909
910
911 --
912 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
913 --
914
915 ALTER TABLE ONLY probe ALTER COLUMN letzte_aenderung SET DEFAULT now();
916
917
918 --
919 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
920 --
921
922 ALTER TABLE ONLY probe_translation ALTER COLUMN id SET DEFAULT nextval('probe_translation_id_seq'::regclass); 927 ALTER TABLE ONLY probe_translation ALTER COLUMN id SET DEFAULT nextval('probe_translation_id_seq'::regclass);
923 928
924 929
925 -- 930 --
926 -- Name: id; Type: DEFAULT; Schema: land; Owner: - 931 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1006 ALTER TABLE ONLY ortszuordnung 1011 ALTER TABLE ONLY ortszuordnung
1007 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id); 1012 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id);
1008 1013
1009 1014
1010 -- 1015 --
1011 -- Name: probe_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1012 --
1013
1014 ALTER TABLE ONLY probe
1015 ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
1016
1017
1018 --
1019 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 1016 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
1020 -- 1017 --
1021 1018
1022 ALTER TABLE ONLY status_protokoll 1019 ALTER TABLE ONLY status_protokoll
1023 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id); 1020 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
1120 ALTER TABLE ONLY ortszuordnung 1117 ALTER TABLE ONLY ortszuordnung
1121 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id); 1118 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id);
1122 1119
1123 1120
1124 -- 1121 --
1125 -- Name: probe_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1126 --
1127
1128 ALTER TABLE ONLY probe
1129 ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
1130
1131
1132 --
1133 -- Name: probe_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 1122 -- Name: probe_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
1134 -- 1123 --
1135 1124
1136 ALTER TABLE ONLY probe_translation 1125 ALTER TABLE ONLY probe_translation
1137 ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id); 1126 ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id);
1357 ALTER TABLE ONLY ortszuordnung 1346 ALTER TABLE ONLY ortszuordnung
1358 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); 1347 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
1359 1348
1360 1349
1361 -- 1350 --
1362 -- Name: probe_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1363 --
1364
1365 ALTER TABLE ONLY probe
1366 ADD CONSTRAINT probe_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
1367
1368
1369 --
1370 -- Name: probe_labor_mst_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1371 --
1372
1373 ALTER TABLE ONLY probe
1374 ADD CONSTRAINT probe_labor_mst_id_fkey FOREIGN KEY (labor_mst_id) REFERENCES stammdaten.mess_stelle(id);
1375
1376
1377 --
1378 -- Name: probe_mst_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1379 --
1380
1381 ALTER TABLE ONLY probe
1382 ADD CONSTRAINT probe_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
1383
1384
1385 --
1386 -- Name: probe_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1387 --
1388
1389 ALTER TABLE ONLY probe
1390 ADD CONSTRAINT probe_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
1391
1392
1393 --
1394 -- Name: probe_probenart_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1395 --
1396
1397 ALTER TABLE ONLY probe
1398 ADD CONSTRAINT probe_probenart_id_fkey FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
1399
1400
1401 --
1402 -- Name: probe_umw_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1403 --
1404
1405 ALTER TABLE ONLY probe
1406 ADD CONSTRAINT probe_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
1407
1408
1409 --
1410 -- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - 1351 -- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
1411 -- 1352 --
1412 1353
1413 ALTER TABLE ONLY status_protokoll 1354 ALTER TABLE ONLY status_protokoll
1414 ADD CONSTRAINT status_protokoll_status_stufe_fkey FOREIGN KEY (status_stufe) REFERENCES stammdaten.status_stufe(id); 1355 ADD CONSTRAINT status_protokoll_status_stufe_fkey FOREIGN KEY (status_stufe) REFERENCES stammdaten.status_stufe(id);
1599 ALTER TABLE ONLY ortszuordnung 1540 ALTER TABLE ONLY ortszuordnung
1600 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; 1541 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
1601 1542
1602 1543
1603 -- 1544 --
1604 -- Name: probe_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1605 --
1606
1607 ALTER TABLE ONLY probe
1608 ADD CONSTRAINT probe_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
1609
1610
1611 --
1612 -- Name: probe_mst_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1613 --
1614
1615 ALTER TABLE ONLY probe
1616 ADD CONSTRAINT probe_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
1617
1618
1619 --
1620 -- Name: probe_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1621 --
1622
1623 ALTER TABLE ONLY probe
1624 ADD CONSTRAINT probe_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
1625
1626
1627 --
1628 -- Name: probe_probenart_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1629 --
1630
1631 ALTER TABLE ONLY probe
1632 ADD CONSTRAINT probe_probenart_id_fkey FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
1633
1634
1635 --
1636 -- Name: probe_translation_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 1545 -- Name: probe_translation_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1637 -- 1546 --
1638 1547
1639 ALTER TABLE ONLY probe_translation 1548 ALTER TABLE ONLY probe_translation
1640 ADD CONSTRAINT probe_translation_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; 1549 ADD CONSTRAINT probe_translation_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
1641 1550
1642 1551
1643 -- 1552 --
1644 -- Name: probe_umw_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1645 --
1646
1647 ALTER TABLE ONLY probe
1648 ADD CONSTRAINT probe_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
1649
1650
1651 --
1652 -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - 1553 -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
1653 -- 1554 --
1654 1555
1655 ALTER TABLE ONLY status_protokoll 1556 ALTER TABLE ONLY status_protokoll
1656 ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; 1557 ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)