Mercurial > dive4elements > river
comparison backend/doc/schema/oracle.sql @ 8683:cfafe5764509
(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; |