Re: unique across two tables

[Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx>]

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