Mercurial > dive4elements > river

comparison backend/doc/schema/oracle.sql @ 8683:cfafe5764509

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
(issue 1796) Scheme change! Add model for seddb_name lookup table.
author Andre Heinecke <andre.heinecke@intevation.de>
date Mon, 13 Apr 2015 11:24:32 +0200
parents 3531f0cee5e1
children 3912da70736b
comparison
equal deleted inserted replaced
8682:5102733c4567 8683:cfafe5764509
253 UNIQUE (river_id, a, b), 253 UNIQUE (river_id, a, b),
254 PRIMARY KEY (id), 254 PRIMARY KEY (id),
255 CHECK (a < b) 255 CHECK (a < b)
256 ); 256 );
257 257
258 -- SEDDB_NAME
259 -- Lookup table for seddb river names
260 CREATE TABLE seddb_name (
261 id NUMBER(38,0) NOT NULL,
262 name VARCHAR2(255) NOT NULL,
263 PRIMARY KEY (id)
264 );
265 INSERT INTO seddb_name (id, name) VALUES (0, 'Rhein');
258 266
259 267
260 -- RIVERS 268 -- RIVERS
261 CREATE SEQUENCE RIVERS_ID_SEQ; 269 CREATE SEQUENCE RIVERS_ID_SEQ;
262 270
265 model_uuid CHAR(36 CHAR) UNIQUE, 273 model_uuid CHAR(36 CHAR) UNIQUE,
266 official_number NUMBER(38,0), 274 official_number NUMBER(38,0),
267 km_up int DEFAULT 0 NOT NULL, 275 km_up int DEFAULT 0 NOT NULL,
268 name VARCHAR2(255) NOT NULL UNIQUE, 276 name VARCHAR2(255) NOT NULL UNIQUE,
269 wst_unit_id NUMBER(38,0) NOT NULL, 277 wst_unit_id NUMBER(38,0) NOT NULL,
278 seddb_name_id NUMBER(38,0),
270 PRIMARY KEY (id), 279 PRIMARY KEY (id),
271 CHECK(km_up IN(0,1)) 280 CHECK(km_up IN(0,1))
272 ); 281 );
273 282
274 283
396 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types; 405 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
397 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values; 406 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
398 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; 407 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
399 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types; 408 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
400 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units; 409 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
410 ALTER TABLE rivers ADD CONSTRAINT cRiversSeddbNames FOREIGN KEY (seddb_name_id) REFERENCES seddb_name;
401 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; 411 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
402 412
403 -- Cascading references 413 -- Cascading references
404 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE; 414 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
405 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE; 415 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE;
http://dive4elements.wald.intevation.org