--- a/schema/gemma.sql	Fri Feb 22 15:15:23 2019 +0100
+++ b/schema/gemma.sql	Fri Feb 22 18:02:23 2019 +0100
@@ -83,13 +83,6 @@
 --
 -- Look-up tables with data that are static in a running system
 --
-CREATE TABLE countries (
-    country_code char(2) PRIMARY KEY -- ISO 3166 country code
-    -- A smallint PK would require even less disk space i.e. on the FK side.
-    -- This might be an issue in case cache space becomes a limiting
-    -- factor when there are many FKs pointing here.
-);
-
 CREATE TABLE language_codes (
     language_code varchar PRIMARY KEY
 );

--- a/schema/isrs.sql	Fri Feb 22 15:15:23 2019 +0100
+++ b/schema/isrs.sql	Fri Feb 22 18:02:23 2019 +0100
@@ -17,22 +17,36 @@
 
 BEGIN;
 
+CREATE TABLE countries (
+    country_code char(2) PRIMARY KEY -- ISO 3166 ALPHA-2 code
+);
+
+CREATE OR REPLACE FUNCTION is_country(code char) RETURNS boolean
+AS $$
+    SELECT EXISTS(SELECT 1 FROM countries WHERE country_code = code)
+$$
+    LANGUAGE sql
+    STABLE PARALLEL SAFE;
+
 --
 -- Types
 --
 
 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
 -- See RIS-Index Encoding Guide
-CREATE TYPE isrs AS (
+CREATE TYPE isrs_base AS (
     country_code char(2), -- ISO 3166 country code
-    -- could be validated against countries table.
     locode char(3), -- without the country code:
     -- http://www.unece.org/cefact/locode/welcome.html
     fairway_section char(5),
     orc char(5), -- Object Reference Code
-    hectometre int -- TODO: should be constrained to five digits
+    hectometre int
 );
 
+CREATE DOMAIN isrs AS isrs_base
+    CHECK (is_country((VALUE).country_code))
+    CHECK ((VALUE).hectometre BETWEEN 0 AND 99999);
+
 CREATE TYPE isrsrange AS RANGE (
     subtype = isrs
 );

--- a/schema/tap_tests_data.sql	Fri Feb 22 15:15:23 2019 +0100
+++ b/schema/tap_tests_data.sql	Fri Feb 22 18:02:23 2019 +0100
@@ -16,7 +16,7 @@
 -- Test data used in *_tests.sql scripts
 --
 
-INSERT INTO countries VALUES ('AT'), ('RO');
+INSERT INTO countries VALUES ('AT'), ('RO'), ('DE');
 
 INSERT INTO users.responsibility_areas VALUES
     ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)),