Re: unique across two tables

Hi Tarlika,

I hope this approach is of interest.

This is how I would design a database to solve the problem -
unfortunately, this
may not be appropriate for your particular situation.

This design ensures that: names of towns are unique within a given
country and
region. plus it can support all the information that the original design
could
provide. I have run this sql using psql in pg 9.1beta2, without any
errors being reported.

Note you will still need business logic, in a trigger or some such, to
ensure
that only one town within a given country and region is marked as the
name of
the town rather than as an alias.

CREATE TABLE country
(
id character varying(3) PRIMARY KEY,
name character varying(50) NOT NULL
);

CREATE TABLE region
(
id character varying(3) PRIMARY KEY,
name character varying(50) NOT NULL
);

CREATE TABLE country_region
(
id serial PRIMARY KEY,
country_fk character varying(3) REFERENCES country (id),
region_fk character varying(3) REFERENCES region (id)
);

CREATE TABLE town
(
id serial PRIMARY KEY,
country_region_fk integer REFERENCES country_region (id),
is_alias boolean DEFAULT true NOT NULL,
"name" character varying(50) NOT NULL,

UNIQUE (country_region_fk, "name")
);

Cheers,
Gavin Flower