Mercurial > dive4elements > river
comparison backend/doc/schema/postgresql-minfo.sql @ 7461:5b5a2cc8210f
Schema change: add lookup table for sediment yield kinds to have its values documented.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Tue, 29 Oct 2013 19:59:59 +0100 |
| parents | 63a24b4fbebf |
| children | 3f6b9fae1637 |
comparison
equal
deleted
inserted
replaced
| 7460:2733b4aae74b | 7461:5b5a2cc8210f |
|---|---|
| 249 upper NUMERIC, | 249 upper NUMERIC, |
| 250 PRIMARY KEY (id) | 250 PRIMARY KEY (id) |
| 251 ); | 251 ); |
| 252 | 252 |
| 253 | 253 |
| 254 --lookup table for sediment yield kinds | |
| 255 CREATE TABLE sediment_yield_kinds ( | |
| 256 id int PRIMARY KEY NOT NULL, | |
| 257 kind VARCHAR(64) NOT NULL | |
| 258 ); | |
| 259 INSERT INTO sediment_yield_kinds (id, kind) VALUES (0, 'non-official'); | |
| 260 INSERT INTO sediment_yield_kinds (id, kind) VALUES (1, 'official'); | |
| 261 | |
| 254 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ; | 262 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ; |
| 255 | 263 |
| 256 CREATE TABLE sediment_yield ( | 264 CREATE TABLE sediment_yield ( |
| 257 id int NOT NULL, | 265 id int NOT NULL, |
| 258 river_id int NOT NULL, | 266 river_id int NOT NULL, |
| 261 time_interval_id int NOT NULL, | 269 time_interval_id int NOT NULL, |
| 262 description VARCHAR(256), | 270 description VARCHAR(256), |
| 263 kind int, | 271 kind int, |
| 264 PRIMARY KEY (id), | 272 PRIMARY KEY (id), |
| 265 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, | 273 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
| 274 CONSTRAINT fk_sy_kind_id FOREIGN KEY (kind) REFERENCES sediment_yield_kinds(id), | |
| 266 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), | 275 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), |
| 267 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id), | 276 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id), |
| 268 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) | 277 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) |
| 269 ); | 278 ); |
| 270 | 279 |