Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-minfo.sql @ 5577:5f91881124ba

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added missing ON DELETE CASCADE to Oracle schema
author Tom Gottfried <tom@intevation.de>
date Fri, 05 Apr 2013 15:42:15 +0200
parents db6c7268b08e
children 88cbe798cbab
comparison
equal deleted inserted replaced
5576:ec60249bd679 5577:5f91881124ba
48 old_elevation_model_id NUMBER(38,0), 48 old_elevation_model_id NUMBER(38,0),
49 range_id NUMBER(38,0), 49 range_id NUMBER(38,0),
50 evaluation_by VARCHAR(255), 50 evaluation_by VARCHAR(255),
51 description VARCHAR(255), 51 description VARCHAR(255),
52 PRIMARY KEY(id), 52 PRIMARY KEY(id),
53 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,
54 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),
55 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),
56 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),
57 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),
58 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) 58 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
59 ); 59 );
60 60
61 61
62 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ; 62 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
63 63
74 description VARCHAR(255), 74 description VARCHAR(255),
75 PRIMARY KEY(id), 75 PRIMARY KEY(id),
76 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),
77 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),
78 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),
79 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
80 ); 80 );
81 81
82 82
83 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; 83 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
84 84
90 uncertainty NUMBER(38,2), 90 uncertainty NUMBER(38,2),
91 data_gap NUMBER(38,2), 91 data_gap NUMBER(38,2),
92 sounding_width NUMBER(38,2), 92 sounding_width NUMBER(38,2),
93 width NUMBER(38,2), 93 width NUMBER(38,2),
94 PRIMARY KEY(id), 94 PRIMARY KEY(id),
95 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
96 ); 96 );
97 97
98 98
99 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ; 99 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
100 100
102 id NUMBER(38,0) NOT NULL, 102 id NUMBER(38,0) NOT NULL,
103 bed_height_epoch_id NUMBER(38,0) NOT NULL, 103 bed_height_epoch_id NUMBER(38,0) NOT NULL,
104 station NUMBER(38,2) NOT NULL, 104 station NUMBER(38,2) NOT NULL,
105 height NUMBER(38,2), 105 height NUMBER(38,2),
106 PRIMARY KEY(id), 106 PRIMARY KEY(id),
107 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
108 ); 108 );
109 109
110 110
111 CREATE SEQUENCE DEPTHS_ID_SEQ; 111 CREATE SEQUENCE DEPTHS_ID_SEQ;
112 112
124 id NUMBER(38,0) NOT NULL, 124 id NUMBER(38,0) NOT NULL,
125 river_id NUMBER(38,0) NOT NULL, 125 river_id NUMBER(38,0) NOT NULL,
126 depth_id NUMBER(38,0) NOT NULL, 126 depth_id NUMBER(38,0) NOT NULL,
127 description VARCHAR(256), 127 description VARCHAR(256),
128 PRIMARY KEY(id), 128 PRIMARY KEY(id),
129 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), 129 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
130 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id) 130 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id)
131 ); 131 );
132 132
133 133
134 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; 134 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
140 shore_offset NUMBER(38,2), 140 shore_offset NUMBER(38,2),
141 density NUMBER(38,2) NOT NULL, 141 density NUMBER(38,2) NOT NULL,
142 description VARCHAR(256), 142 description VARCHAR(256),
143 year NUMBER(38,0), 143 year NUMBER(38,0),
144 PRIMARY KEY(id), 144 PRIMARY KEY(id),
145 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) 145 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE
146 ); 146 );
147 147
148 148
149 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; 149 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
150 150
151 CREATE TABLE morphologic_width ( 151 CREATE TABLE morphologic_width (
152 id NUMBER(38,0) NOT NULL, 152 id NUMBER(38,0) NOT NULL,
153 river_id NUMBER(38,0) NOT NULL, 153 river_id NUMBER(38,0) NOT NULL,
154 unit_id NUMBER(38,0) NOT NULL, 154 unit_id NUMBER(38,0) NOT NULL,
155 PRIMARY KEY(id), 155 PRIMARY KEY(id),
156 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id), 156 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE,
157 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) 157 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
158 ); 158 );
159 159
160 160
161 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; 161 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
165 morphologic_width_id NUMBER(38,0) NOT NULL, 165 morphologic_width_id NUMBER(38,0) NOT NULL,
166 station NUMBER(38,3) NOT NULL, 166 station NUMBER(38,3) NOT NULL,
167 width NUMBER(38,3) NOT NULL, 167 width NUMBER(38,3) NOT NULL,
168 description VARCHAR(256), 168 description VARCHAR(256),
169 PRIMARY KEY(id), 169 PRIMARY KEY(id),
170 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) 170 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE
171 ); 171 );
172 172
173 173
174 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; 174 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;
175 175
179 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance 179 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance
180 value NUMBER(38,3) NOT NULL, 180 value NUMBER(38,3) NOT NULL,
181 lower_discharge VARCHAR(16) NOT NULL, 181 lower_discharge VARCHAR(16) NOT NULL,
182 upper_discharge VARCHAR(16), 182 upper_discharge VARCHAR(16),
183 PRIMARY KEY(id), 183 PRIMARY KEY(id),
184 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) 184 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
185 ); 185 );
186 186
187 187
188 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; 188 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;
189 189
190 CREATE TABLE flow_velocity_model ( 190 CREATE TABLE flow_velocity_model (
191 id NUMBER(38,0) NOT NULL, 191 id NUMBER(38,0) NOT NULL,
192 discharge_zone_id NUMBER(38,0) NOT NULL, 192 discharge_zone_id NUMBER(38,0) NOT NULL,
193 description VARCHAR(256), 193 description VARCHAR(256),
194 PRIMARY KEY (id), 194 PRIMARY KEY (id),
195 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) 195 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE
196 ); 196 );
197 197
198 198
199 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; 199 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
200 200
205 q NUMBER(38,3) NOT NULL, 205 q NUMBER(38,3) NOT NULL,
206 total_channel NUMBER(38,3) NOT NULL, 206 total_channel NUMBER(38,3) NOT NULL,
207 main_channel NUMBER(38,3) NOT NULL, 207 main_channel NUMBER(38,3) NOT NULL,
208 shear_stress NUMBER(38,3) NOT NULL, 208 shear_stress NUMBER(38,3) NOT NULL,
209 PRIMARY KEY(id), 209 PRIMARY KEY(id),
210 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) 210 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE
211 ); 211 );
212 212
213 213
214 214
215 CREATE SEQUENCE FV_MEASURE_ID_SEQ; 215 CREATE SEQUENCE FV_MEASURE_ID_SEQ;
217 CREATE TABLE flow_velocity_measurements ( 217 CREATE TABLE flow_velocity_measurements (
218 id NUMBER(38,0) NOT NULL, 218 id NUMBER(38,0) NOT NULL,
219 river_id NUMBER(38,0) NOT NULL, 219 river_id NUMBER(38,0) NOT NULL,
220 description VARCHAR(256), 220 description VARCHAR(256),
221 PRIMARY KEY (id), 221 PRIMARY KEY (id),
222 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) 222 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
223 ); 223 );
224 224
225 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; 225 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
226 226
227 CREATE TABLE flow_velocity_measure_values ( 227 CREATE TABLE flow_velocity_measure_values (
232 w NUMBER(38,3) NOT NULL, 232 w NUMBER(38,3) NOT NULL,
233 q NUMBER(38,3) NOT NULL, 233 q NUMBER(38,3) NOT NULL,
234 v NUMBER(38,3) NOT NULL, 234 v NUMBER(38,3) NOT NULL,
235 description VARCHAR(256), 235 description VARCHAR(256),
236 PRIMARY KEY (id), 236 PRIMARY KEY (id),
237 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) 237 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE
238 ); 238 );
239 239
240 240
241 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; 241 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
242 242
257 grain_fraction_id NUMBER(38,0), 257 grain_fraction_id NUMBER(38,0),
258 unit_id NUMBER(38,0) NOT NULL, 258 unit_id NUMBER(38,0) NOT NULL,
259 time_interval_id NUMBER(38,0) NOT NULL, 259 time_interval_id NUMBER(38,0) NOT NULL,
260 description VARCHAR(256), 260 description VARCHAR(256),
261 PRIMARY KEY (id), 261 PRIMARY KEY (id),
262 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), 262 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
263 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), 263 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
264 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id), 264 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
265 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) 265 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
266 ); 266 );
267 267
272 id NUMBER(38,0) NOT NULL, 272 id NUMBER(38,0) NOT NULL,
273 sediment_yield_id NUMBER(38,0) NOT NULL, 273 sediment_yield_id NUMBER(38,0) NOT NULL,
274 station NUMBER(38,3) NOT NULL, 274 station NUMBER(38,3) NOT NULL,
275 value NUMBER(38,3) NOT NULL, 275 value NUMBER(38,3) NOT NULL,
276 PRIMARY KEY (id), 276 PRIMARY KEY (id),
277 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) 277 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE
278 ); 278 );
279 279
280 280
281 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; 281 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
282 CREATE TABLE measurement_station ( 282 CREATE TABLE measurement_station (
307 id NUMBER(38,0) NOT NULL, 307 id NUMBER(38,0) NOT NULL,
308 river_id NUMBER(38,0) NOT NULL, 308 river_id NUMBER(38,0) NOT NULL,
309 time_interval_id NUMBER(38,0) NOT NULL, 309 time_interval_id NUMBER(38,0) NOT NULL,
310 description VARCHAR(256), 310 description VARCHAR(256),
311 PRIMARY KEY (id), 311 PRIMARY KEY (id),
312 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), 312 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
313 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) 313 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
314 ); 314 );
315 315
316 316
317 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; 317 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
328 ntot NUMBER(38,0), 328 ntot NUMBER(38,0),
329 noutl NUMBER(38,0), 329 noutl NUMBER(38,0),
330 cferguson NUMBER(38,20), 330 cferguson NUMBER(38,20),
331 cduan NUMBER(38,20), 331 cduan NUMBER(38,20),
332 PRIMARY KEY (id), 332 PRIMARY KEY (id),
333 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id), 333 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE,
334 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id) 334 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id)
335 ); 335 );
http://dive4elements.wald.intevation.org