Mercurial > dive4elements > river

changeset 4991:b79eb203032d dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
author Tom Gottfried <tom.gottfried@intevation.de>
date Thu, 14 Feb 2013 17:36:10 +0100
parents f4d61ac84b76
children ecc6fd57b630
files flys-backend/doc/schema/postgresql-minfo.sql flys-backend/doc/schema/postgresql-spatial.sql flys-backend/doc/schema/postgresql.sql
diffstat 3 files changed, 63 insertions(+), 63 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/doc/schema/postgresql-minfo.sql	Thu Feb 14 10:54:02 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-minfo.sql	Thu Feb 14 17:36:10 2013 +0100
@@ -46,12 +46,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
 );
 
 
@@ -72,7 +72,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
 );
 
 
@@ -88,7 +88,7 @@
     sounding_width          NUMERIC,
     width                   NUMERIC,
     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
 );
 
 
@@ -100,7 +100,7 @@
     station                 NUMERIC NOT NULL,
     height                  NUMERIC,
     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
 );
 
 
@@ -125,7 +125,7 @@
     unit_id     int 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),
     CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
 );
@@ -141,7 +141,7 @@
     description         VARCHAR(256),
     year                int,
     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
 );
 
 
@@ -152,7 +152,7 @@
     river_id    int NOT NULL,
     unit_id     int 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)
 );
 
@@ -166,7 +166,7 @@
     width                   NUMERIC 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
 );
 
 
@@ -180,7 +180,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,8 +192,8 @@
     discharge_zone_id   int NOT NULL,
     description         VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
-    CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
+    CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
+    CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE
 );
 
 
@@ -208,7 +208,7 @@
     main_channel            NUMERIC NOT NULL,
     shear_stress            NUMERIC 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
 );
 
 
@@ -220,7 +220,7 @@
     river_id    int 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;
@@ -235,7 +235,7 @@
     v               NUMERIC 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
 );
 
 
@@ -262,7 +262,7 @@
     time_interval_id    int 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)
@@ -277,7 +277,7 @@
     station             NUMERIC NOT NULL,
     value               NUMERIC 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
 );
 
 
@@ -289,7 +289,7 @@
     unit_id     int NOT NULL,
     description VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
 );
 
@@ -301,7 +301,7 @@
     waterlevel_id   int NOT NULL,
     q               NUMERIC NOT NULL,
     PRIMARY KEY (id),
-    CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id)
+    CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id) ON DELETE CASCADE
 );
 
 
@@ -313,7 +313,7 @@
     station                 NUMERIC NOT NULL,
     w                       NUMERIC NOT NULL,
     PRIMARY KEY (id),
-    CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id)
+    CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id) ON DELETE CASCADE
 );
 
 
@@ -325,7 +325,7 @@
     unit_id     int NOT NULL,
     description VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id),
+    CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id) ON DELETE CASCADE,
     CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
 );
 
@@ -337,7 +337,7 @@
     difference_id   int NOT NULL,
     description     VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id)
+    CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id) ON DELETE CASCADE
 );
 
 
@@ -349,7 +349,7 @@
     station     NUMERIC NOT NULL,
     value       NUMERIC NOT NULL,
     PRIMARY KEY (id),
-    CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
+    CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id) ON DELETE CASCADE
 );
 
 
@@ -367,9 +367,9 @@
 	operator				 VARCHAR(64),
 	comment					 VARCHAR(512),
 	PRIMARY KEY (id),
-	CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
-	CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id),
-	CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id),
+	CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
+	CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE,
+	CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE,
 	CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id),
 	UNIQUE (river_id, station)
 );
@@ -383,7 +383,7 @@
     time_interval_id int 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)
 );
 
@@ -400,6 +400,6 @@
     a              NUMERIC NOT NULL,
     b              NUMERIC NOT NULL,
     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
 );
 COMMIT;
--- a/flys-backend/doc/schema/postgresql-spatial.sql	Thu Feb 14 10:54:02 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Thu Feb 14 17:36:10 2013 +0100
@@ -4,7 +4,7 @@
 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
 CREATE TABLE river_axes (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     kind     int             NOT NULL DEFAULT 0,
     name     VARCHAR(64),
     path     VARCHAR(256)
@@ -18,7 +18,7 @@
 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
 CREATE TABLE river_axes_km (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     km       FLOAT8 NOT NULL,
     name     VARCHAR(64),
     path     VARCHAR(256)
@@ -31,7 +31,7 @@
 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
 CREATE TABLE cross_section_tracks (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     km       FLOAT8 NOT NULL,
     z        FLOAT8 NOT NULL DEFAULT 0,
     name     VARCHAR(64),
@@ -45,7 +45,7 @@
 CREATE SEQUENCE LINES_ID_SEQ;
 CREATE TABLE lines (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     kind     VARCHAR(16) NOT NULL,
     z        FLOAT8 DEFAULT 0,
     name     VARCHAR(64),
@@ -62,7 +62,7 @@
 CREATE SEQUENCE BUILDINGS_ID_SEQ;
 CREATE TABLE buildings (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     name     VARCHAR(256),
     path     VARCHAR(256)
 );
@@ -74,7 +74,7 @@
 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
 CREATE TABLE fixpoints (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     x        FLOAT8,
     y        FLOAT8,
     km       FLOAT8 NOT NULL,
@@ -90,7 +90,7 @@
 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
 CREATE TABLE floodplain (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     name     VARCHAR(64),
     path     VARCHAR(256)
 );
@@ -102,7 +102,7 @@
 CREATE SEQUENCE DEM_ID_SEQ;
 CREATE TABLE dem (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     -- XXX Should we use the ranges table instead?
     name            VARCHAR(64),
     lower           FLOAT8,
@@ -124,7 +124,7 @@
 CREATE SEQUENCE CATCHMENT_ID_SEQ;
 CREATE TABLE catchment (
     id int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     area FLOAT8,
     name VARCHAR(256),
     path     VARCHAR(256)
@@ -169,7 +169,7 @@
     ogr_fid int,
     kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
     fed_state_id int REFERENCES fed_states(id),
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     name VARCHAR(256),
     path VARCHAR(256),
     offical INT DEFAULT 0,
@@ -194,7 +194,7 @@
     ogr_fid int,
     kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
     fed_state_id int REFERENCES fed_states(id),
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     name VARCHAR,
     path VARCHAR,
     offical INT DEFAULT 0,
@@ -227,7 +227,7 @@
 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
 CREATE TABLE floodmaps (
     id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id),
+    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       varchar(64) NOT NULL,
     kind       int NOT NULL,
     diff       FLOAT8,
@@ -244,7 +244,7 @@
 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
 CREATE TABLE hydr_boundaries (
     id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id),
+    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       VARCHAR(255),
     kind       int, -- 1 BfG / 2 Land / 3 Sonstige
     sectie     int, -- 1 stromführend / 2 stromspeichernd
@@ -258,7 +258,7 @@
 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
 CREATE TABLE hydr_boundaries_poly (
     id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id),
+    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       VARCHAR(255),
     kind       int, -- 1 BfG / 2 Land / 3 Sonstige
     sectie     int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich
@@ -272,7 +272,7 @@
 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
 CREATE TABLE gauge_location (
     id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id),
+    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       VARCHAR(255),
     path       VARCHAR(256)
 );
--- a/flys-backend/doc/schema/postgresql.sql	Thu Feb 14 10:54:02 2013 +0100
+++ b/flys-backend/doc/schema/postgresql.sql	Thu Feb 14 17:36:10 2013 +0100
@@ -31,7 +31,7 @@
 
 CREATE TABLE ranges (
     id       int PRIMARY KEY NOT NULL,
-    river_id int             NOT NULL REFERENCES rivers(id),
+    river_id int             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
     a        NUMERIC         NOT NULL,
     b        NUMERIC,
     UNIQUE (river_id, a, b)
@@ -68,7 +68,7 @@
 
 CREATE TABLE annotations (
     id           int PRIMARY KEY NOT NULL,
-    range_id     int             NOT NULL REFERENCES ranges(id),
+    range_id     int             NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
     attribute_id int             NOT NULL REFERENCES attributes(id),
     position_id  int REFERENCES positions(id),
     edge_id      int REFERENCES edges(id),
@@ -81,7 +81,7 @@
 CREATE TABLE gauges (
     id              int PRIMARY KEY NOT NULL,
     name            VARCHAR(256)    NOT NULL,
-    river_id        int             NOT NULL REFERENCES rivers(id),
+    river_id        int             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
     station         NUMERIC         NOT NULL UNIQUE,
     aeo             NUMERIC         NOT NULL,
     official_number int8                     UNIQUE,
@@ -89,7 +89,7 @@
     -- Pegelnullpunkt
     datum    NUMERIC NOT NULL,
     -- Streckengueltigkeit
-    range_id int REFERENCES ranges (id),
+    range_id int REFERENCES ranges (id) ON DELETE CASCADE,
 
     UNIQUE (name, river_id),
     UNIQUE (river_id, station)
@@ -128,7 +128,7 @@
 
 CREATE TABLE main_values (
     id             int PRIMARY KEY NOT NULL,
-    gauge_id       int NOT NULL REFERENCES gauges(id),
+    gauge_id       int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
     named_value_id int NOT NULL REFERENCES named_main_values(id),
     value          NUMERIC NOT NULL,
 
@@ -143,7 +143,7 @@
 
 CREATE TABLE discharge_tables (
     id               int PRIMARY KEY NOT NULL,
-    gauge_id         int NOT NULL REFERENCES gauges(id),
+    gauge_id         int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
     description      VARCHAR(256) NOT NULL,
     bfg_id           VARCHAR(50),
     kind             int NOT NULL DEFAULT 0,
@@ -158,7 +158,7 @@
 
 CREATE TABLE discharge_table_values (
     id       int PRIMARY KEY NOT NULL,
-    table_id int NOT NULL REFERENCES discharge_tables(id),
+    table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
     q        NUMERIC NOT NULL,
     w        NUMERIC NOT NULL,
 
@@ -170,7 +170,7 @@
 
 CREATE TABLE wsts (
     id          int PRIMARY KEY NOT NULL,
-    river_id    int NOT NULL REFERENCES rivers(id),
+    river_id    int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
     description VARCHAR(256) NOT NULL,
     kind        int NOT NULL DEFAULT 0,
     -- TODO: more meta infos
@@ -182,7 +182,7 @@
 
 CREATE TABLE wst_columns (
     id          int PRIMARY KEY NOT NULL,
-    wst_id      int NOT NULL REFERENCES wsts(id),
+    wst_id      int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE,
     name        VARCHAR(256) NOT NULL,
     description VARCHAR(256),
     position    int NOT NULL DEFAULT 0,
@@ -198,7 +198,7 @@
 
 CREATE TABLE wst_column_values (
     id            int PRIMARY KEY NOT NULL,
-    wst_column_id int NOT NULL REFERENCES wst_columns(id),
+    wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
     position      NUMERIC NOT NULL,
     w             NUMERIC NOT NULL,
 
@@ -211,7 +211,7 @@
 
 CREATE TABLE wst_q_ranges (
     id       int PRIMARY KEY NOT NULL,
-    range_id int NOT NULL REFERENCES ranges(id),
+    range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
     q        NUMERIC NOT NULL
 );
 
@@ -220,8 +220,8 @@
 
 CREATE TABLE wst_column_q_ranges (
     id             int PRIMARY KEY NOT NULL,
-    wst_column_id  int NOT NULL REFERENCES wst_columns(id),
-    wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id),
+    wst_column_id  int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
+    wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE,
 
     UNIQUE (wst_column_id, wst_q_range_id)
 );
@@ -277,7 +277,7 @@
 
 CREATE TABLE cross_sections (
     id               int PRIMARY KEY NOT NULL,
-    river_id         int             NOT NULL REFERENCES rivers(id),
+    river_id         int             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
     time_interval_id int                      REFERENCES time_intervals(id),
     description      VARCHAR(256)
 );
@@ -287,7 +287,7 @@
 CREATE TABLE cross_section_lines (
     id               int PRIMARY KEY NOT NULL,
     km               NUMERIC         NOT NULL,
-    cross_section_id int             NOT NULL REFERENCES cross_sections(id),
+    cross_section_id int             NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE,
     UNIQUE (km, cross_section_id)
 );
 
@@ -295,7 +295,7 @@
 
 CREATE TABLE cross_section_points (
     id                    int PRIMARY KEY NOT NULL,
-    cross_section_line_id int             NOT NULL REFERENCES cross_section_lines(id),
+    cross_section_line_id int             NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
     col_pos               int             NOT NULL,
     x                     NUMERIC         NOT NULL,
     y                     NUMERIC         NOT NULL,
@@ -314,7 +314,7 @@
 
 CREATE TABLE hyks (
     id          int PRIMARY KEY NOT NULL,
-    river_id    int             NOT NULL REFERENCES rivers(id),
+    river_id    int             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
     description VARCHAR(256)    NOT NULL
 );
 
@@ -322,7 +322,7 @@
 
 CREATE TABLE hyk_entries (
     id          int PRIMARY KEY NOT NULL,
-    hyk_id      int             NOT NULL REFERENCES hyks(id),
+    hyk_id      int             NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
     km          NUMERIC         NOT NULL,
     measure     TIMESTAMP,
     UNIQUE (hyk_id, km)
@@ -333,7 +333,7 @@
 CREATE TABLE hyk_formations (
     id            int PRIMARY KEY NOT NULL,
     formation_num int             NOT NULL DEFAULT 0,
-    hyk_entry_id  int             NOT NULL REFERENCES hyk_entries(id),
+    hyk_entry_id  int             NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE,
     top           NUMERIC         NOT NULL,
     bottom        NUMERIC         NOT NULL,
     distance_vl   NUMERIC         NOT NULL,
@@ -354,7 +354,7 @@
 
 CREATE TABLE hyk_flow_zones (
     id           int PRIMARY KEY NOT NULL,
-    formation_id int             NOT NULL REFERENCES hyk_formations(id),
+    formation_id int             NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE,
     type_id      int             NOT NULL REFERENCES hyk_flow_zone_types(id),
     a            NUMERIC         NOT NULL,
     b            NUMERIC         NOT NULL,
http://dive4elements.wald.intevation.org