Mercurial > dive4elements > river
comparison backend/doc/schema/postgresql-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 |
|---|---|
| 307 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE, | 307 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE, |
| 308 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id) | 308 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id) |
| 309 ); | 309 ); |
| 310 | 310 |
| 311 | 311 |
| 312 CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ; | |
| 313 | |
| 314 CREATE TABLE sediment_loads ( | |
| 315 id int NOT NULL, | |
| 316 grain_fraction_id int NOT NULL, | |
| 317 time_interval_id int NOT NULL, | |
| 318 sq_time_interval_id int, | |
| 319 description VARCHAR(256), | |
| 320 kind int, | |
| 321 PRIMARY KEY (id), | |
| 322 CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind) | |
| 323 REFERENCES sediment_yield_kinds(id), | |
| 324 CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id) | |
| 325 REFERENCES grain_fraction(id), | |
| 326 CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id) | |
| 327 REFERENCES time_intervals(id), | |
| 328 CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id) | |
| 329 REFERENCES time_intervals(id) | |
| 330 ); | |
| 331 | |
| 332 | |
| 333 CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ; | |
| 334 | |
| 335 CREATE TABLE sediment_load_values ( | |
| 336 id int NOT NULL, | |
| 337 sediment_load_id int NOT NULL, | |
| 338 measurement_station_id int NOT NULL, | |
| 339 value DOUBLE PRECISION NOT NULL, | |
| 340 PRIMARY KEY (id), | |
| 341 CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id) | |
| 342 REFERENCES sediment_loads(id) ON DELETE CASCADE, | |
| 343 CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id) | |
| 344 REFERENCES measurement_station(id) ON DELETE CASCADE | |
| 345 ); | |
| 346 | |
| 347 | |
| 312 CREATE SEQUENCE SQ_RELATION_ID_SEQ; | 348 CREATE SEQUENCE SQ_RELATION_ID_SEQ; |
| 313 | 349 |
| 314 CREATE TABLE sq_relation ( | 350 CREATE TABLE sq_relation ( |
| 315 id int NOT NULL, | 351 id int NOT NULL, |
| 316 river_id int NOT NULL, | 352 river_id int NOT NULL, |