Mercurial > gemma
changeset 1821:332e42a2088d
DB schema: Made primary key of waterway.gauges an integer for staging purposes.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 16 Jan 2019 11:28:33 +0100 |
parents | ba24a6e3e64d |
children | f1351a58da35 |
files | schema/gemma.sql |
diffstat | 1 files changed, 5 insertions(+), 4 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Jan 16 11:21:12 2019 +0100 +++ b/schema/gemma.sql Wed Jan 16 11:28:33 2019 +0100 @@ -236,7 +236,8 @@ ) CREATE TABLE gauges ( - location isrs PRIMARY KEY CHECK( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + location isrs UNIQUE NOT NULL KEY CHECK( (location).orc SIMILAR TO 'G[[:digit:]]{4}' AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), objname varchar NOT NULL, @@ -256,7 +257,7 @@ FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE gauges_reference_water_levels ( - gauge_id isrs NOT NULL REFERENCES gauges, + gauge_id isrs NOT NULL REFERENCES gauges(location), reference_water_level varchar(20) NOT NULL REFERENCES reference_water_levels, PRIMARY KEY (gauge_id, reference_water_level), @@ -265,7 +266,7 @@ CREATE TABLE gauge_measurements ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - fk_gauge_id isrs NOT NULL REFERENCES gauges, + fk_gauge_id isrs NOT NULL REFERENCES gauges(location), measure_date timestamp with time zone NOT NULL, country_code char(2) NOT NULL REFERENCES countries, -- TODO: add relations to stuff provided as enumerations @@ -406,7 +407,7 @@ CREATE TABLE bottlenecks ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, bottleneck_id varchar UNIQUE NOT NULL, - fk_g_fid isrs NOT NULL REFERENCES gauges, + fk_g_fid isrs NOT NULL REFERENCES gauges(location), -- XXX: DRC references "ch. 3.1.1", which does not exist in document. objnam varchar, nobjnm varchar,