Re: unique across two tables - Mailing list pgsql-general

From Gavin Flower
Subject Re: unique across two tables
Date
Msg-id 4E01AD7F.2060807@archidevsys.co.nz
Whole thread Raw
In response to unique across two tables  (Tarlika Elisabeth Schmitz <postgresql4@numerixtechnology.de>)
Responses Re: unique across two tables  (Tarlika Elisabeth Schmitz <postgresql4@numerixtechnology.de>)
List pgsql-general

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

pgsql-general by date:

Previous
From: simon
Date:
Subject: Re: Error - could not get socket error status: Invalid argument
Next
From: Radosław Smogura
Date:
Subject: Re: Error - could not get socket error status: Invalid argument