Mercurial > dive4elements > river
comparison backend/doc/schema/oracle-minfo.sql @ 8706:94cddb00f934
(issue1750) Apply schema changes to oracle, which are already done for PostgreSQL.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 24 Apr 2015 15:06:38 +0200 |
| parents | 1a4ad0d4231a |
| children |
comparison
equal
deleted
inserted
replaced
| 8705:682a26e4158b | 8706:94cddb00f934 |
|---|---|
| 323 -- store name of reference gauges here too, as not all are in gauges | 323 -- store name of reference gauges here too, as not all are in gauges |
| 324 reference_gauge_name VARCHAR2(64 CHAR), | 324 reference_gauge_name VARCHAR2(64 CHAR), |
| 325 operator VARCHAR2(64 CHAR), | 325 operator VARCHAR2(64 CHAR), |
| 326 commentary VARCHAR2(512 CHAR), | 326 commentary VARCHAR2(512 CHAR), |
| 327 PRIMARY KEY (id), | 327 PRIMARY KEY (id), |
| 328 CHECK(measurement_type IN ('Geschiebe', 'Schwebstoff')), | |
| 328 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) | 329 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) |
| 329 REFERENCES ranges(id) ON DELETE CASCADE, | 330 REFERENCES ranges(id) ON DELETE CASCADE, |
| 330 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) | 331 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) |
| 331 REFERENCES gauges(id) ON DELETE CASCADE, | 332 REFERENCES gauges(id) ON DELETE CASCADE, |
| 332 CONSTRAINT fk_time_interval_id FOREIGN KEY (time_interval_id) | 333 CONSTRAINT fk_time_interval_id FOREIGN KEY (time_interval_id) |
| 372 | 373 |
| 373 CREATE SEQUENCE SQ_RELATION_ID_SEQ; | 374 CREATE SEQUENCE SQ_RELATION_ID_SEQ; |
| 374 | 375 |
| 375 CREATE TABLE sq_relation ( | 376 CREATE TABLE sq_relation ( |
| 376 id NUMBER(38,0) NOT NULL, | 377 id NUMBER(38,0) NOT NULL, |
| 377 river_id NUMBER(38,0) NOT NULL, | |
| 378 time_interval_id NUMBER(38,0) NOT NULL, | 378 time_interval_id NUMBER(38,0) NOT NULL, |
| 379 description VARCHAR(256), | 379 description VARCHAR(256), |
| 380 PRIMARY KEY (id), | 380 PRIMARY KEY (id), |
| 381 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, | |
| 382 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) | 381 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) |
| 383 ); | 382 ); |
| 384 | 383 |
| 385 | 384 |
| 386 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; | 385 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; |
| 397 ntot NUMBER(38,0), | 396 ntot NUMBER(38,0), |
| 398 noutl NUMBER(38,0), | 397 noutl NUMBER(38,0), |
| 399 cferguson NUMBER(38,20), | 398 cferguson NUMBER(38,20), |
| 400 cduan NUMBER(38,20), | 399 cduan NUMBER(38,20), |
| 401 PRIMARY KEY (id), | 400 PRIMARY KEY (id), |
| 401 UNIQUE(sq_relation_id, measurement_station_id, parameter), | |
| 402 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE, | 402 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE, |
| 403 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id) ON DELETE CASCADE | 403 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id) ON DELETE CASCADE |
| 404 ); | 404 ); |