Mercurial > dive4elements > river
diff flys-backend/doc/schema/postgresql.sql @ 4991:b79eb203032d dami
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 | 20b6ebf23916 |
children | 646c154477fe |
line wrap: on
line diff
--- 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,