changeset 5577:5f91881124ba

added missing ON DELETE CASCADE to Oracle schema
author Tom Gottfried <tom@intevation.de>
date Fri, 05 Apr 2013 15:42:15 +0200
parents ec60249bd679
children c0eca1287e0e
files flys-backend/doc/schema/oracle-minfo.sql flys-backend/doc/schema/oracle-spatial.sql
diffstat 2 files changed, 19 insertions(+), 19 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/doc/schema/oracle-minfo.sql	Fri Apr 05 14:56:36 2013 +0200
+++ b/flys-backend/doc/schema/oracle-minfo.sql	Fri Apr 05 15:42:15 2013 +0200
@@ -50,12 +50,12 @@
     evaluation_by           VARCHAR(255),
     description             VARCHAR(255),
     PRIMARY KEY(id),
-    CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
     CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
     CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
     CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
-    CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
+    CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
 );
 
 
@@ -76,7 +76,7 @@
     CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
     CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
     CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
-    CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
+    CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
 );
 
 
@@ -92,7 +92,7 @@
     sounding_width          NUMBER(38,2),
     width                   NUMBER(38,2),
     PRIMARY KEY(id),
-    CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
+    CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE
 );
 
 
@@ -104,7 +104,7 @@
     station                 NUMBER(38,2) NOT NULL,
     height                  NUMBER(38,2),
     PRIMARY KEY(id),
-    CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
+    CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ON DELETE CASCADE
 );
 
 
@@ -126,7 +126,7 @@
     depth_id    NUMBER(38,0) NOT NULL,
     description VARCHAR(256),
     PRIMARY KEY(id),
-    CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id)
 );
 
@@ -142,7 +142,7 @@
     description         VARCHAR(256),
     year                NUMBER(38,0),
     PRIMARY KEY(id),
-    CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id)
+    CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE
 );
 
 
@@ -153,7 +153,7 @@
     river_id    NUMBER(38,0) NOT NULL,
     unit_id     NUMBER(38,0) NOT NULL,
     PRIMARY KEY(id),
-    CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
 );
 
@@ -167,7 +167,7 @@
     width                   NUMBER(38,3) NOT NULL,
     description             VARCHAR(256),
     PRIMARY KEY(id),
-    CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
+    CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE
 );
 
 
@@ -181,7 +181,7 @@
     lower_discharge         VARCHAR(16)  NOT NULL,
     upper_discharge         VARCHAR(16),
     PRIMARY KEY(id),
-    CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id)
+    CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
 );
 
 
@@ -192,7 +192,7 @@
     discharge_zone_id   NUMBER(38,0) NOT NULL,
     description         VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
+    CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE
 );
 
 
@@ -207,7 +207,7 @@
     main_channel            NUMBER(38,3) NOT NULL,
     shear_stress            NUMBER(38,3) NOT NULL,
     PRIMARY KEY(id),
-    CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id)
+    CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE
 );
 
 
@@ -219,7 +219,7 @@
     river_id    NUMBER(38,0) NOT NULL,
     description VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id)
+    CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
 );
 
 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
@@ -234,7 +234,7 @@
     v               NUMBER(38,3) NOT NULL,
     description     VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
+    CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE
 );
 
 
@@ -259,7 +259,7 @@
     time_interval_id    NUMBER(38,0) NOT NULL,
     description         VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
     CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
     CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
@@ -274,7 +274,7 @@
     station             NUMBER(38,3) NOT NULL,
     value               NUMBER(38,3) NOT NULL,
     PRIMARY KEY (id),
-    CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
+    CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE
 );
 
 
@@ -309,7 +309,7 @@
     time_interval_id NUMBER(38,0) NOT NULL,
     description      VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
 );
 
@@ -330,6 +330,6 @@
     cferguson                NUMBER(38,20),
     cduan                    NUMBER(38,20),
     PRIMARY KEY (id),
-    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id),
+    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE,
     CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id)
 );
--- a/flys-backend/doc/schema/oracle-spatial.sql	Fri Apr 05 14:56:36 2013 +0200
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Fri Apr 05 15:42:15 2013 +0200
@@ -219,7 +219,7 @@
     GEOM MDSYS.SDO_GEOMETRY,
     kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id),
     fed_state_id NUMBER(2) REFERENCES fed_states(id),
-    river_id NUMBER(38) REFERENCES rivers(id),
+    river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
     name VARCHAR(256),
     path VARCHAR(256),
     official NUMBER DEFAULT 0,

http://dive4elements.wald.intevation.org