Mercurial > gemma

changeset 1821:332e42a2088d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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,