Mercurial > dive4elements > river
comparison backend/doc/schema/oracle-minfo.sql @ 8017:bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Tue, 08 Jul 2014 19:44:54 +0200 |
| parents | a88e308567a1 |
| children | ad614eff7a9b |
comparison
equal
deleted
inserted
replaced
| 8015:b0d4b1a3c345 | 8017:bcb05a98bb3f |
|---|---|
| 306 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE, | 306 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE, |
| 307 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id) | 307 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id) |
| 308 ); | 308 ); |
| 309 | 309 |
| 310 | 310 |
| 311 CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ; | |
| 312 | |
| 313 CREATE TABLE sediment_loads ( | |
| 314 id int NOT NULL, | |
| 315 grain_fraction_id int NOT NULL, | |
| 316 time_interval_id int NOT NULL, | |
| 317 sq_time_interval_id int, | |
| 318 description VARCHAR(256), | |
| 319 kind int, | |
| 320 PRIMARY KEY (id), | |
| 321 CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind) | |
| 322 REFERENCES sediment_yield_kinds(id), | |
| 323 CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id) | |
| 324 REFERENCES grain_fraction(id), | |
| 325 CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id) | |
| 326 REFERENCES time_intervals(id), | |
| 327 CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id) | |
| 328 REFERENCES time_intervals(id) | |
| 329 ); | |
| 330 | |
| 331 | |
| 332 CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ; | |
| 333 | |
| 334 CREATE TABLE sediment_load_values ( | |
| 335 id int NOT NULL, | |
| 336 sediment_load_id int NOT NULL, | |
| 337 measurement_station_id int NOT NULL, | |
| 338 value DOUBLE PRECISION NOT NULL, | |
| 339 PRIMARY KEY (id), | |
| 340 CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id) | |
| 341 REFERENCES sediment_loads(id) ON DELETE CASCADE, | |
| 342 CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id) | |
| 343 REFERENCES measurement_station(id) ON DELETE CASCADE | |
| 344 ); | |
| 345 | |
| 346 | |
| 311 CREATE SEQUENCE SQ_RELATION_ID_SEQ; | 347 CREATE SEQUENCE SQ_RELATION_ID_SEQ; |
| 312 | 348 |
| 313 CREATE TABLE sq_relation ( | 349 CREATE TABLE sq_relation ( |
| 314 id NUMBER(38,0) NOT NULL, | 350 id NUMBER(38,0) NOT NULL, |
| 315 river_id NUMBER(38,0) NOT NULL, | 351 river_id NUMBER(38,0) NOT NULL, |