Thanks for your elaborate reply and providing me these insights on the concept on immutable functions.
Regarding your question about what the CHECK constraint should achieve, I had abstracted by use case into Books/Book Types, which may have caused some vagueness. The actual use case are the following tables.
------------------------------------------------------------------------
-- Describes a type of geographical entity.
--
-- Columns:
-- gtype_id: The primary key and identifier code of type.
-- feature_class: A character indicating the feature class.
-- display_name: The display name of the type.
-- description: A description of the type.
-- is_enabled: Indicates if type is globally enabled.
-- is_system: Indicates if type is system and therefor may not
-- be modified.
-- is_deleted: Indicates if type is considered deleted.
------------------------------------------------------------------------
CREATE TABLE gtypes(
feature_code varchar(64) NOT NULL PRIMARY KEY,
feature_class varchar(1) NOT NULL,
display_name varchar(128) NOT NULL,
--administrative_depth integer NOT NULL CHECK(administrative_depth > 0) DEFAULT 0,
description text,
is_enabled boolean NOT NULL DEFAULT TRUE,
is_system boolean NOT NULL DEFAULT FALSE,
is_deleted boolean NOT NULL DEFAULT FALSE,
UNIQUE (feature_code, feature_class)
);
------------------------------------------------------------------------
-- Describes a geographical entity.
--
-- Columns:
-- feature_id: An unsigned long integer specifying the primary
-- key.
-- gtype_id: A string referencing a gtype instance.
-- ascii_name: The entity name as ascii.
-- display_name: A string containing the display name of entity,
-- English preferred.
-- native_name: A string containing the native name of entity.
-- valid_from: A date specifying the valid from date of entity.
-- valid_to: A date specifying the valid to date of entity.
-- primary_datasource: A string indicating the primary datasource
-- of entity.
-- is_deleted: A boolean indicating if entity is considered deleted.
-- created: A timestamp with time zone indicating the date and
-- time entity was inserted.
------------------------------------------------------------------------
CREATE SEQUENCE feature_id_seq START WITH 100000;
CREATE TABLE features(
feature_id bigint NOT NULL PRIMARY KEY DEFAULT nextval('feature_id_seq'),
feature_code varchar(64) NOT NULL -- Immutable
REFERENCES gtypes (feature_code)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE INITIALLY DEFERRED,
feature_name varchar(255),
feature_name_native varchar(255),
ascii_name varchar(512) NOT NULL,
display_name varchar(512) NOT NULL,
native_name varchar(512),
abbreviation varchar(64),
timezone varchar(64),
valid_from date NOT NULL DEFAULT now()::date,
valid_to date,
created timestamp with time zone NOT NULL DEFAULT now(),
modified timestamp with time zone NOT NULL DEFAULT now(),
primary_datasource varchar(64) NOT NULL, -- Immutable
is_deleted boolean NOT NULL DEFAULT FALSE,
CHECK (valid_to >= valid_from),
UNIQUE (feature_id, feature_code)
);
------------------------------------------------------------------------
-- trigger function to handle immutable fields on the features table.
------------------------------------------------------------------------
CREATE FUNCTION chk_features_immutable()
RETURNS TRIGGER AS
$$
BEGIN
CASE
WHEN OLD.feature_code != NEW.feature_code THEN
RAISE SQLSTATE '23514' USING MESSAGE = 'features.feature_code is immutable';
WHEN OLD.primary_datasource != NEW.primary_datasource THEN
RAISE SQLSTATE '23514' USING MESSAGE = 'features.primary_datasource is immutable';
ELSE
RETURN NEW;
END CASE;
END;
$$
LANGUAGE 'plpgsql';
CREATE TRIGGER tr_chk_features_immutable
BEFORE UPDATE ON features
FOR EACH ROW
EXECUTE PROCEDURE chk_features_immutable();
------------------------------------------------------------------------
-- Returns the feature code of a given feature.
--
-- Args:
-- feature_id bigint: The primary key of a features entity.
--
-- Returns:
-- varchar(64)
------------------------------------------------------------------------
CREATE FUNCTION features_get_feature_code(int8)
RETURNS varchar(64) AS
$$
DECLARE
fcode varchar(64);
BEGIN
SELECT feature_code INTO fcode FROM features WHERE feature_id = $1;
IF NOT FOUND THEN
RAISE EXCEPTION 'Entity does not exist.';
END IF;
RETURN fcode;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;
The "features" table contains countries, administrative divisions, cities, postal codes, landmarks, sights, rivers, mountains; any kind of geographical feature.
There is also a table called "persons" (irrelevant fields omitted):
CREATE TABLE persons(
person_id int8 NOT NULL PRIMARY KEY,
place_of_birth_id int8
REFERENCES features (feature_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
INITIALLY IMMEDIATE,
CHECK (features_get_feature_code(place_of_birth_id) ~ 'PC.*|ADM.*|PP.*')
);
The CHECK constraint should achieve that "persons.place_of_birth_id" is always a country, or a (first_order) adminitrative division, or a city (which is defined by "features.gtype_id").
Though this could be done by creating a multi-column foreign key on ("features.feature_id","features.gtype_id"), this would violate the principles of normalization.
Of course this could also be achieved by a TRIGGER, but that seems a little redundant to me.
Kind regards,
Cochise Ruhulessin
