Mercurial > gemma
changeset 65:cad19acc53b3
Avoid duplicate direct relations in composite primary keys.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 25 May 2018 21:45:08 +0200 |
parents | 1e9589cf9c00 |
children | 21df0e95db4a |
files | wamos.sql |
diffstat | 1 files changed, 9 insertions(+), 12 deletions(-) [+] |
line wrap: on
line diff
--- a/wamos.sql Fri May 25 21:34:02 2018 +0200 +++ b/wamos.sql Fri May 25 21:45:08 2018 +0200 @@ -173,11 +173,10 @@ FOR EACH ROW EXECUTE PROCEDURE update_date_info(); CREATE TABLE wamos.fa_reference_values ( - bottleneck_id varchar NOT NULL REFERENCES bottlenecks, - surdat date NOT NULL, - FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + fairway_availability_id NOT NULL REFERENCES fairway_availability, level_of_service smallint NOT NULL REFERENCES levels_of_service, - PRIMARY KEY (bottleneck_id, surdat, level_of_service), + PRIMARY KEY (fairway_availability_id, level_of_service), fairway_depth smallint, fairway_width smallint, fairway_radius int, @@ -186,23 +185,21 @@ ); CREATE TABLE wamos.bottleneck_pdfs ( - bottleneck_id varchar NOT NULL REFERENCES bottlenecks, - surdat date NOT NULL, - FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + fairway_availability_id NOT NULL REFERENCES fairway_availability, profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? - PRIMARY KEY (bottleneck_id, surdat, profile_pdf_url), + PRIMARY KEY (fairway_availability_id, profile_pdf_url), pdf_generation_date timestamp with time zone NOT NULL, source_organization varchar NOT NULL ); CREATE TABLE wamos.effective_fairway_availability ( - bottleneck_id varchar NOT NULL REFERENCES bottlenecks, - surdat date NOT NULL, - FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + fairway_availability_id NOT NULL REFERENCES fairway_availability, measure_date timestamp with time zone NOT NULL, level_of_service smallint NOT NULL REFERENCES levels_of_service, - PRIMARY KEY (bottleneck_id, surdat, measure_date, level_of_service), + PRIMARY KEY (fairway_availability_id, measure_date, level_of_service), available_depth_value smallint, available_width_value smallint, water_level_value smallint,