Re: [GENERAL] unique across two tables

[Gavin Flower]

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