Mercurial > lada > lada-server
comparison db_schema/stammdaten_schema.sql @ 1020:3779455b621c schema-update
add stammdaten.kta and stammdaten.ortszusatz
add table definion and testdata
change constraitns of stammdaten.ort
add column hoehe_ueber_nn to stammdaten.ort
| author | Michael Stanko <mstanko@bfs.de> |
|---|---|
| date | Thu, 07 Jul 2016 11:46:14 +0200 |
| parents | 178ac8ee17c0 |
| children | 330dec7ee0bb |
comparison
equal
deleted
inserted
replaced
| 1018:178ac8ee17c0 | 1020:3779455b621c |
|---|---|
| 368 CREATE TABLE ort_typ ( | 368 CREATE TABLE ort_typ ( |
| 369 id smallint PRIMARY KEY, | 369 id smallint PRIMARY KEY, |
| 370 ort_typ character varying(60) | 370 ort_typ character varying(60) |
| 371 ); | 371 ); |
| 372 | 372 |
| 373 CREATE TABLE kta ( | |
| 374 id serial NOT NULL, | |
| 375 code character varying(7), | |
| 376 bezeichnung character varying(80), | |
| 377 CONSTRAINT kta_pkey PRIMARY KEY (id) | |
| 378 ); | |
| 379 COMMENT ON TABLE kta | |
| 380 IS 'kernteschnische Anlagen'; | |
| 381 | |
| 382 CREATE TABLE ortszusatz ( | |
| 383 id serial NOT NULL, | |
| 384 code character varying(7), | |
| 385 bezeichnung character varying(80), | |
| 386 CONSTRAINT ortszusatz_pkey PRIMARY KEY (id) | |
| 387 ); | |
| 373 | 388 |
| 374 CREATE TABLE ort ( | 389 CREATE TABLE ort ( |
| 375 id serial PRIMARY KEY, | 390 id serial PRIMARY KEY, |
| 376 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, | 391 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, |
| 377 ort_id character varying(10) NOT NULL, | 392 ort_id character varying(10) NOT NULL, |
| 397 zustaendigkeit character varying(10), | 412 zustaendigkeit character varying(10), |
| 398 mp_art character varying(10), | 413 mp_art character varying(10), |
| 399 aktiv character(1), | 414 aktiv character(1), |
| 400 anlage_id integer, | 415 anlage_id integer, |
| 401 oz_id integer, | 416 oz_id integer, |
| 417 hoehe_ueber_nn real, | |
| 402 UNIQUE(ort_id, netzbetreiber_id) | 418 UNIQUE(ort_id, netzbetreiber_id) |
| 403 ); | 419 ); |
| 420 | |
| 404 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | 421 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); |
| 405 | 422 |
| 406 ALTER TABLE ONLY ort | 423 ALTER TABLE ONLY ort |
| 407 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id); | 424 ADD CONSTRAINT ort_kta_fkey FOREIGN KEY (anlage_id) REFERENCES kta(id); |
| 408 | 425 |
| 409 ALTER TABLE ONLY ort | 426 ALTER TABLE ONLY ort |
| 410 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id); | 427 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id); |
| 411 | 428 |
| 412 | 429 |