Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql-minfo.sql @ 4991:b79eb203032d dami
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
author | Tom Gottfried <tom.gottfried@intevation.de> |
---|---|
date | Thu, 14 Feb 2013 17:36:10 +0100 |
parents | 504cd5801785 |
children | 646c154477fe |
comparison
equal
deleted
inserted
replaced
4989:f4d61ac84b76 | 4991:b79eb203032d |
---|---|
44 old_elevation_model_id int, | 44 old_elevation_model_id int, |
45 range_id int NOT NULL, | 45 range_id int NOT NULL, |
46 evaluation_by VARCHAR(255), | 46 evaluation_by VARCHAR(255), |
47 description VARCHAR(255), | 47 description VARCHAR(255), |
48 PRIMARY KEY(id), | 48 PRIMARY KEY(id), |
49 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), | 49 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
50 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), | 50 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), |
51 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id), | 51 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id), |
52 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), | 52 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), |
53 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), | 53 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), |
54 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) | 54 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE |
55 ); | 55 ); |
56 | 56 |
57 | 57 |
58 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ; | 58 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ; |
59 | 59 |
70 description VARCHAR(255), | 70 description VARCHAR(255), |
71 PRIMARY KEY(id), | 71 PRIMARY KEY(id), |
72 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), | 72 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), |
73 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), | 73 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), |
74 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), | 74 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), |
75 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) | 75 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE |
76 ); | 76 ); |
77 | 77 |
78 | 78 |
79 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; | 79 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; |
80 | 80 |
86 uncertainty NUMERIC, | 86 uncertainty NUMERIC, |
87 data_gap NUMERIC, | 87 data_gap NUMERIC, |
88 sounding_width NUMERIC, | 88 sounding_width NUMERIC, |
89 width NUMERIC, | 89 width NUMERIC, |
90 PRIMARY KEY(id), | 90 PRIMARY KEY(id), |
91 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) | 91 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE |
92 ); | 92 ); |
93 | 93 |
94 | 94 |
95 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ; | 95 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ; |
96 | 96 |
98 id int NOT NULL, | 98 id int NOT NULL, |
99 bed_height_epoch_id int NOT NULL, | 99 bed_height_epoch_id int NOT NULL, |
100 station NUMERIC NOT NULL, | 100 station NUMERIC NOT NULL, |
101 height NUMERIC, | 101 height NUMERIC, |
102 PRIMARY KEY(id), | 102 PRIMARY KEY(id), |
103 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) | 103 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ON DELETE CASCADE |
104 ); | 104 ); |
105 | 105 |
106 | 106 |
107 CREATE SEQUENCE DEPTHS_ID_SEQ; | 107 CREATE SEQUENCE DEPTHS_ID_SEQ; |
108 | 108 |
123 river_id int NOT NULL, | 123 river_id int NOT NULL, |
124 depth_id int NOT NULL, | 124 depth_id int NOT NULL, |
125 unit_id int NOT NULL, | 125 unit_id int NOT NULL, |
126 description VARCHAR(256), | 126 description VARCHAR(256), |
127 PRIMARY KEY(id), | 127 PRIMARY KEY(id), |
128 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), | 128 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
129 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), | 129 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), |
130 CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) | 130 CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) |
131 ); | 131 ); |
132 | 132 |
133 | 133 |
139 station NUMERIC NOT NULL, | 139 station NUMERIC NOT NULL, |
140 density NUMERIC NOT NULL, | 140 density NUMERIC NOT NULL, |
141 description VARCHAR(256), | 141 description VARCHAR(256), |
142 year int, | 142 year int, |
143 PRIMARY KEY(id), | 143 PRIMARY KEY(id), |
144 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) | 144 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE |
145 ); | 145 ); |
146 | 146 |
147 | 147 |
148 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; | 148 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; |
149 | 149 |
150 CREATE TABLE morphologic_width ( | 150 CREATE TABLE morphologic_width ( |
151 id int NOT NULL, | 151 id int NOT NULL, |
152 river_id int NOT NULL, | 152 river_id int NOT NULL, |
153 unit_id int NOT NULL, | 153 unit_id int NOT NULL, |
154 PRIMARY KEY(id), | 154 PRIMARY KEY(id), |
155 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id), | 155 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
156 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) | 156 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) |
157 ); | 157 ); |
158 | 158 |
159 | 159 |
160 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; | 160 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; |
164 morphologic_width_id int NOT NULL, | 164 morphologic_width_id int NOT NULL, |
165 station NUMERIC NOT NULL, | 165 station NUMERIC NOT NULL, |
166 width NUMERIC NOT NULL, | 166 width NUMERIC NOT NULL, |
167 description VARCHAR(256), | 167 description VARCHAR(256), |
168 PRIMARY KEY(id), | 168 PRIMARY KEY(id), |
169 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) | 169 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE |
170 ); | 170 ); |
171 | 171 |
172 | 172 |
173 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; | 173 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; |
174 | 174 |
178 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance | 178 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance |
179 value NUMERIC NOT NULL, | 179 value NUMERIC NOT NULL, |
180 lower_discharge VARCHAR(16) NOT NULL, | 180 lower_discharge VARCHAR(16) NOT NULL, |
181 upper_discharge VARCHAR(16), | 181 upper_discharge VARCHAR(16), |
182 PRIMARY KEY(id), | 182 PRIMARY KEY(id), |
183 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) | 183 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE |
184 ); | 184 ); |
185 | 185 |
186 | 186 |
187 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; | 187 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; |
188 | 188 |
190 id int NOT NULL, | 190 id int NOT NULL, |
191 river_id int NOT NULL, | 191 river_id int NOT NULL, |
192 discharge_zone_id int NOT NULL, | 192 discharge_zone_id int NOT NULL, |
193 description VARCHAR(256), | 193 description VARCHAR(256), |
194 PRIMARY KEY (id), | 194 PRIMARY KEY (id), |
195 CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), | 195 CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
196 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) | 196 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE |
197 ); | 197 ); |
198 | 198 |
199 | 199 |
200 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; | 200 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; |
201 | 201 |
206 q NUMERIC NOT NULL, | 206 q NUMERIC NOT NULL, |
207 total_channel NUMERIC NOT NULL, | 207 total_channel NUMERIC NOT NULL, |
208 main_channel NUMERIC NOT NULL, | 208 main_channel NUMERIC NOT NULL, |
209 shear_stress NUMERIC NOT NULL, | 209 shear_stress NUMERIC NOT NULL, |
210 PRIMARY KEY(id), | 210 PRIMARY KEY(id), |
211 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) | 211 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE |
212 ); | 212 ); |
213 | 213 |
214 | 214 |
215 | 215 |
216 CREATE SEQUENCE FV_MEASURE_ID_SEQ; | 216 CREATE SEQUENCE FV_MEASURE_ID_SEQ; |
218 CREATE TABLE flow_velocity_measurements ( | 218 CREATE TABLE flow_velocity_measurements ( |
219 id int NOT NULL, | 219 id int NOT NULL, |
220 river_id int NOT NULL, | 220 river_id int NOT NULL, |
221 description VARCHAR(256), | 221 description VARCHAR(256), |
222 PRIMARY KEY (id), | 222 PRIMARY KEY (id), |
223 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) | 223 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE |
224 ); | 224 ); |
225 | 225 |
226 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; | 226 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; |
227 | 227 |
228 CREATE TABLE flow_velocity_measure_values ( | 228 CREATE TABLE flow_velocity_measure_values ( |
233 w NUMERIC NOT NULL, | 233 w NUMERIC NOT NULL, |
234 q NUMERIC NOT NULL, | 234 q NUMERIC NOT NULL, |
235 v NUMERIC NOT NULL, | 235 v NUMERIC NOT NULL, |
236 description VARCHAR(256), | 236 description VARCHAR(256), |
237 PRIMARY KEY (id), | 237 PRIMARY KEY (id), |
238 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) | 238 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE |
239 ); | 239 ); |
240 | 240 |
241 | 241 |
242 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; | 242 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; |
243 | 243 |
260 grain_fraction_id int, | 260 grain_fraction_id int, |
261 unit_id int NOT NULL, | 261 unit_id int NOT NULL, |
262 time_interval_id int NOT NULL, | 262 time_interval_id int NOT NULL, |
263 description VARCHAR(256), | 263 description VARCHAR(256), |
264 PRIMARY KEY (id), | 264 PRIMARY KEY (id), |
265 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), | 265 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
266 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), | 266 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), | 267 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) | 268 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) |
269 ); | 269 ); |
270 | 270 |
275 id int NOT NULL, | 275 id int NOT NULL, |
276 sediment_yield_id int NOT NULL, | 276 sediment_yield_id int NOT NULL, |
277 station NUMERIC NOT NULL, | 277 station NUMERIC NOT NULL, |
278 value NUMERIC NOT NULL, | 278 value NUMERIC NOT NULL, |
279 PRIMARY KEY (id), | 279 PRIMARY KEY (id), |
280 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) | 280 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE |
281 ); | 281 ); |
282 | 282 |
283 | 283 |
284 CREATE SEQUENCE WATERLEVEL_ID_SEQ; | 284 CREATE SEQUENCE WATERLEVEL_ID_SEQ; |
285 | 285 |
287 id int NOT NULL, | 287 id int NOT NULL, |
288 river_id int NOT NULL, | 288 river_id int NOT NULL, |
289 unit_id int NOT NULL, | 289 unit_id int NOT NULL, |
290 description VARCHAR(256), | 290 description VARCHAR(256), |
291 PRIMARY KEY (id), | 291 PRIMARY KEY (id), |
292 CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), | 292 CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
293 CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) | 293 CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) |
294 ); | 294 ); |
295 | 295 |
296 | 296 |
297 CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ; | 297 CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ; |
299 CREATE TABLE waterlevel_q_range ( | 299 CREATE TABLE waterlevel_q_range ( |
300 id int NOT NULL, | 300 id int NOT NULL, |
301 waterlevel_id int NOT NULL, | 301 waterlevel_id int NOT NULL, |
302 q NUMERIC NOT NULL, | 302 q NUMERIC NOT NULL, |
303 PRIMARY KEY (id), | 303 PRIMARY KEY (id), |
304 CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id) | 304 CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id) ON DELETE CASCADE |
305 ); | 305 ); |
306 | 306 |
307 | 307 |
308 CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ; | 308 CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ; |
309 | 309 |
311 id int NOT NULL, | 311 id int NOT NULL, |
312 waterlevel_q_range_id int NOT NULL, | 312 waterlevel_q_range_id int NOT NULL, |
313 station NUMERIC NOT NULL, | 313 station NUMERIC NOT NULL, |
314 w NUMERIC NOT NULL, | 314 w NUMERIC NOT NULL, |
315 PRIMARY KEY (id), | 315 PRIMARY KEY (id), |
316 CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id) | 316 CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id) ON DELETE CASCADE |
317 ); | 317 ); |
318 | 318 |
319 | 319 |
320 CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ; | 320 CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ; |
321 | 321 |
323 id int NOT NULL, | 323 id int NOT NULL, |
324 river_id int NOT NULL, | 324 river_id int NOT NULL, |
325 unit_id int NOT NULL, | 325 unit_id int NOT NULL, |
326 description VARCHAR(256), | 326 description VARCHAR(256), |
327 PRIMARY KEY (id), | 327 PRIMARY KEY (id), |
328 CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id), | 328 CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id) ON DELETE CASCADE, |
329 CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) | 329 CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) |
330 ); | 330 ); |
331 | 331 |
332 | 332 |
333 CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ; | 333 CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ; |
335 CREATE TABLE waterlevel_difference_column ( | 335 CREATE TABLE waterlevel_difference_column ( |
336 id int NOT NULL, | 336 id int NOT NULL, |
337 difference_id int NOT NULL, | 337 difference_id int NOT NULL, |
338 description VARCHAR(256), | 338 description VARCHAR(256), |
339 PRIMARY KEY (id), | 339 PRIMARY KEY (id), |
340 CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id) | 340 CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id) ON DELETE CASCADE |
341 ); | 341 ); |
342 | 342 |
343 | 343 |
344 CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ; | 344 CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ; |
345 | 345 |
347 id int NOT NULL, | 347 id int NOT NULL, |
348 column_id int NOT NULL, | 348 column_id int NOT NULL, |
349 station NUMERIC NOT NULL, | 349 station NUMERIC NOT NULL, |
350 value NUMERIC NOT NULL, | 350 value NUMERIC NOT NULL, |
351 PRIMARY KEY (id), | 351 PRIMARY KEY (id), |
352 CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id) | 352 CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id) ON DELETE CASCADE |
353 ); | 353 ); |
354 | 354 |
355 | 355 |
356 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; | 356 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; |
357 CREATE TABLE measurement_station ( | 357 CREATE TABLE measurement_station ( |
365 reference_gauge_id int, | 365 reference_gauge_id int, |
366 observation_timerange_id int, | 366 observation_timerange_id int, |
367 operator VARCHAR(64), | 367 operator VARCHAR(64), |
368 comment VARCHAR(512), | 368 comment VARCHAR(512), |
369 PRIMARY KEY (id), | 369 PRIMARY KEY (id), |
370 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), | 370 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
371 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id), | 371 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE, |
372 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id), | 372 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE, |
373 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id), | 373 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id), |
374 UNIQUE (river_id, station) | 374 UNIQUE (river_id, station) |
375 ); | 375 ); |
376 | 376 |
377 | 377 |
381 id int NOT NULL, | 381 id int NOT NULL, |
382 river_id int NOT NULL, | 382 river_id int NOT NULL, |
383 time_interval_id int NOT NULL, | 383 time_interval_id int NOT NULL, |
384 description VARCHAR(256), | 384 description VARCHAR(256), |
385 PRIMARY KEY (id), | 385 PRIMARY KEY (id), |
386 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), | 386 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
387 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) | 387 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) |
388 ); | 388 ); |
389 | 389 |
390 | 390 |
391 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; | 391 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; |
398 function VARCHAR(32) NOT NULL, | 398 function VARCHAR(32) NOT NULL, |
399 km NUMERIC NOT NULL, | 399 km NUMERIC NOT NULL, |
400 a NUMERIC NOT NULL, | 400 a NUMERIC NOT NULL, |
401 b NUMERIC NOT NULL, | 401 b NUMERIC NOT NULL, |
402 PRIMARY KEY (id), | 402 PRIMARY KEY (id), |
403 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) | 403 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE |
404 ); | 404 ); |
405 COMMIT; | 405 COMMIT; |