Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-minfo.sql @ 5379:61bf64b102bc mapgenfix

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge with default branch
author Christian Lins <christian.lins@intevation.de>
date Fri, 22 Mar 2013 11:25:54 +0100
parents 6dd354e7abfc
children e88d55d01cf5
comparison
equal deleted inserted replaced
5175:cfc5540a4eec 5379:61bf64b102bc
18 unit_id int NOT NULL, 18 unit_id int NOT NULL,
19 PRIMARY KEY(id), 19 PRIMARY KEY(id),
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) 20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
21 ); 21 );
22 22
23 CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ; 23
24 24 -- lookup table for bedheight types
25 CREATE TABLE bed_height_type ( 25 CREATE TABLE bed_height_type (
26 id int NOT NULL, 26 id int NOT NULL,
27 name VARCHAR(16) NOT NULL, 27 name VARCHAR(64) NOT NULL,
28 description VARCHAR(255),
29 PRIMARY KEY(id) 28 PRIMARY KEY(id)
30 ); 29 );
31 30 INSERT INTO bed_height_type VALUES (1, 'Querprofile');
31 INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung');
32 INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen');
33 INSERT INTO bed_height_type VALUES (4, 'DGM');
34 INSERT INTO bed_height_type VALUES (5, 'TIN');
35 INSERT INTO bed_height_type VALUES (6, 'Modell');
32 36
33 37
34 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; 38 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
35 39
36 CREATE TABLE bed_height_single ( 40 CREATE TABLE bed_height_single (
44 old_elevation_model_id int, 48 old_elevation_model_id int,
45 range_id int NOT NULL, 49 range_id int NOT NULL,
46 evaluation_by VARCHAR(255), 50 evaluation_by VARCHAR(255),
47 description VARCHAR(255), 51 description VARCHAR(255),
48 PRIMARY KEY(id), 52 PRIMARY KEY(id),
49 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), 53 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), 54 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), 55 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), 56 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), 57 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) 58 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
55 ); 59 );
56 60
57 61
58 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ; 62 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
59 63
70 description VARCHAR(255), 74 description VARCHAR(255),
71 PRIMARY KEY(id), 75 PRIMARY KEY(id),
72 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), 76 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), 77 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), 78 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) 79 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
76 ); 80 );
77 81
78 82
79 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; 83 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
80 84
86 uncertainty NUMERIC, 90 uncertainty NUMERIC,
87 data_gap NUMERIC, 91 data_gap NUMERIC,
88 sounding_width NUMERIC, 92 sounding_width NUMERIC,
89 width NUMERIC, 93 width NUMERIC,
90 PRIMARY KEY(id), 94 PRIMARY KEY(id),
91 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) 95 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE
92 ); 96 );
93 97
94 98
95 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ; 99 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
96 100
98 id int NOT NULL, 102 id int NOT NULL,
99 bed_height_epoch_id int NOT NULL, 103 bed_height_epoch_id int NOT NULL,
100 station NUMERIC NOT NULL, 104 station NUMERIC NOT NULL,
101 height NUMERIC, 105 height NUMERIC,
102 PRIMARY KEY(id), 106 PRIMARY KEY(id),
103 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) 107 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ON DELETE CASCADE
104 ); 108 );
105 109
106 110
107 CREATE SEQUENCE DEPTHS_ID_SEQ; 111 CREATE SEQUENCE DEPTHS_ID_SEQ;
108 112
123 river_id int NOT NULL, 127 river_id int NOT NULL,
124 depth_id int NOT NULL, 128 depth_id int NOT NULL,
125 unit_id int NOT NULL, 129 unit_id int NOT NULL,
126 description VARCHAR(256), 130 description VARCHAR(256),
127 PRIMARY KEY(id), 131 PRIMARY KEY(id),
128 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), 132 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), 133 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) 134 CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
131 ); 135 );
132 136
133 137
139 station NUMERIC NOT NULL, 143 station NUMERIC NOT NULL,
140 density NUMERIC NOT NULL, 144 density NUMERIC NOT NULL,
141 description VARCHAR(256), 145 description VARCHAR(256),
142 year int, 146 year int,
143 PRIMARY KEY(id), 147 PRIMARY KEY(id),
144 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) 148 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE
145 ); 149 );
146 150
147 151
148 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; 152 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
149 153
150 CREATE TABLE morphologic_width ( 154 CREATE TABLE morphologic_width (
151 id int NOT NULL, 155 id int NOT NULL,
152 river_id int NOT NULL, 156 river_id int NOT NULL,
153 unit_id int NOT NULL, 157 unit_id int NOT NULL,
154 PRIMARY KEY(id), 158 PRIMARY KEY(id),
155 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id), 159 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) 160 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
157 ); 161 );
158 162
159 163
160 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; 164 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
164 morphologic_width_id int NOT NULL, 168 morphologic_width_id int NOT NULL,
165 station NUMERIC NOT NULL, 169 station NUMERIC NOT NULL,
166 width NUMERIC NOT NULL, 170 width NUMERIC NOT NULL,
167 description VARCHAR(256), 171 description VARCHAR(256),
168 PRIMARY KEY(id), 172 PRIMARY KEY(id),
169 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) 173 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE
170 ); 174 );
171 175
172 176
173 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; 177 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;
174 178
178 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance 182 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance
179 value NUMERIC NOT NULL, 183 value NUMERIC NOT NULL,
180 lower_discharge VARCHAR(16) NOT NULL, 184 lower_discharge VARCHAR(16) NOT NULL,
181 upper_discharge VARCHAR(16), 185 upper_discharge VARCHAR(16),
182 PRIMARY KEY(id), 186 PRIMARY KEY(id),
183 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) 187 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
184 ); 188 );
185 189
186 190
187 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; 191 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;
188 192
189 CREATE TABLE flow_velocity_model ( 193 CREATE TABLE flow_velocity_model (
190 id int NOT NULL, 194 id int NOT NULL,
191 river_id int NOT NULL,
192 discharge_zone_id int NOT NULL, 195 discharge_zone_id int NOT NULL,
193 description VARCHAR(256), 196 description VARCHAR(256),
194 PRIMARY KEY (id), 197 PRIMARY KEY (id),
195 CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), 198 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE
196 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
197 ); 199 );
198 200
199 201
200 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; 202 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
201 203
206 q NUMERIC NOT NULL, 208 q NUMERIC NOT NULL,
207 total_channel NUMERIC NOT NULL, 209 total_channel NUMERIC NOT NULL,
208 main_channel NUMERIC NOT NULL, 210 main_channel NUMERIC NOT NULL,
209 shear_stress NUMERIC NOT NULL, 211 shear_stress NUMERIC NOT NULL,
210 PRIMARY KEY(id), 212 PRIMARY KEY(id),
211 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) 213 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE
212 ); 214 );
213 215
214 216
215 217
216 CREATE SEQUENCE FV_MEASURE_ID_SEQ; 218 CREATE SEQUENCE FV_MEASURE_ID_SEQ;
218 CREATE TABLE flow_velocity_measurements ( 220 CREATE TABLE flow_velocity_measurements (
219 id int NOT NULL, 221 id int NOT NULL,
220 river_id int NOT NULL, 222 river_id int NOT NULL,
221 description VARCHAR(256), 223 description VARCHAR(256),
222 PRIMARY KEY (id), 224 PRIMARY KEY (id),
223 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) 225 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
224 ); 226 );
225 227
226 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; 228 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
227 229
228 CREATE TABLE flow_velocity_measure_values ( 230 CREATE TABLE flow_velocity_measure_values (
233 w NUMERIC NOT NULL, 235 w NUMERIC NOT NULL,
234 q NUMERIC NOT NULL, 236 q NUMERIC NOT NULL,
235 v NUMERIC NOT NULL, 237 v NUMERIC NOT NULL,
236 description VARCHAR(256), 238 description VARCHAR(256),
237 PRIMARY KEY (id), 239 PRIMARY KEY (id),
238 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) 240 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE
239 ); 241 );
240 242
241 243
242 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; 244 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
243 245
244 CREATE TABLE grain_fraction ( 246 CREATE TABLE grain_fraction (
245 id int NOT NULL, 247 id int NOT NULL,
246 name VARCHAR(64) NOT NULL, 248 name VARCHAR(64) NOT NULL,
247 lower NUMERIC, 249 lower NUMERIC,
248 upper NUMERIC, 250 upper NUMERIC,
249 unit_id int, 251 PRIMARY KEY (id),
250 PRIMARY KEY (id),
251 CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
252 ); 252 );
253 253
254 254
255 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ; 255 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
256 256
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 );
282
283
284 CREATE SEQUENCE WATERLEVEL_ID_SEQ;
285
286 CREATE TABLE waterlevel (
287 id int NOT NULL,
288 river_id int NOT NULL,
289 unit_id int NOT NULL,
290 description VARCHAR(256),
291 PRIMARY KEY (id),
292 CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
293 CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
294 );
295
296
297 CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ;
298
299 CREATE TABLE waterlevel_q_range (
300 id int NOT NULL,
301 waterlevel_id int NOT NULL,
302 q NUMERIC NOT NULL,
303 PRIMARY KEY (id),
304 CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id)
305 );
306
307
308 CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ;
309
310 CREATE TABLE waterlevel_values (
311 id int NOT NULL,
312 waterlevel_q_range_id int NOT NULL,
313 station NUMERIC NOT NULL,
314 w NUMERIC NOT NULL,
315 PRIMARY KEY (id),
316 CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id)
317 );
318
319
320 CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ;
321
322 CREATE TABLE waterlevel_difference (
323 id int NOT NULL,
324 river_id int NOT NULL,
325 unit_id int NOT NULL,
326 description VARCHAR(256),
327 PRIMARY KEY (id),
328 CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id),
329 CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
330 );
331
332
333 CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ;
334
335 CREATE TABLE waterlevel_difference_column (
336 id int NOT NULL,
337 difference_id int NOT NULL,
338 description VARCHAR(256),
339 PRIMARY KEY (id),
340 CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id)
341 );
342
343
344 CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ;
345
346 CREATE TABLE waterlevel_difference_values (
347 id int NOT NULL,
348 column_id int NOT NULL,
349 station NUMERIC NOT NULL,
350 value NUMERIC NOT NULL,
351 PRIMARY KEY (id),
352 CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
353 ); 281 );
354 282
355 283
356 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; 284 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
357 CREATE TABLE measurement_station ( 285 CREATE TABLE measurement_station (
358 id int NOT NULL, 286 id int NOT NULL,
359 name VARCHAR(256) NOT NULL, 287 name VARCHAR(256) NOT NULL,
360 river_id int NOT NULL, 288 river_id int NOT NULL,
361 station NUMERIC NOT NULL, 289 station NUMERIC NOT NULL,
362 range_id int NOT NULL, 290 range_id int NOT NULL,
363 measurement_type VARCHAR(64) NOT NULL, 291 measurement_type VARCHAR(64) NOT NULL,
364 riverside VARCHAR(16), 292 riverside VARCHAR(16),
365 reference_gauge_id int, 293 reference_gauge_id int,
366 observation_timerange_id int, 294 observation_timerange_id int,
367 operator VARCHAR(64), 295 operator VARCHAR(64),
368 comment VARCHAR(512), 296 description VARCHAR(512),
369 PRIMARY KEY (id), 297 PRIMARY KEY (id),
370 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), 298 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), 299 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), 300 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), 301 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id),
374 UNIQUE (river_id, station) 302 UNIQUE (river_id, station)
375 ); 303 );
376 304
377 305
378 CREATE SEQUENCE SQ_RELATION_ID_SEQ; 306 CREATE SEQUENCE SQ_RELATION_ID_SEQ;
379 307
381 id int NOT NULL, 309 id int NOT NULL,
382 river_id int NOT NULL, 310 river_id int NOT NULL,
383 time_interval_id int NOT NULL, 311 time_interval_id int NOT NULL,
384 description VARCHAR(256), 312 description VARCHAR(256),
385 PRIMARY KEY (id), 313 PRIMARY KEY (id),
386 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), 314 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) 315 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
388 ); 316 );
389 317
390 318
391 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; 319 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
398 function VARCHAR(32) NOT NULL, 326 function VARCHAR(32) NOT NULL,
399 km NUMERIC NOT NULL, 327 km NUMERIC NOT NULL,
400 a NUMERIC NOT NULL, 328 a NUMERIC NOT NULL,
401 b NUMERIC NOT NULL, 329 b NUMERIC NOT NULL,
402 PRIMARY KEY (id), 330 PRIMARY KEY (id),
403 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) 331 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE
404 ); 332 );
405 COMMIT; 333 COMMIT;
http://dive4elements.wald.intevation.org