Re: unique across two tables

Hello Gavin,

On Wed, 22 Jun 2011 20:53:19 +1200
Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:

> [...]
>This design ensures that: names of towns are unique within a given
>country and >region.
>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 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")

Many thanks, also to David, Misa and Merlin for taking the time to post.

The concept of having separate tables for country/region/town sprang
from another discussion how to derive this information from freeform
text. Therefore alias tables might contain common
abbreviations/misspellings (which I can't detect with soundex, etc.). I
even have a table of non-standard country codes and I'd find it messy
to store these invalid variations in my "clean" country/region tables.

For the time being I plumped for a solution found in a thread Alban
Hertroys had pointed out:
http://postgresql.1045698.n5.nabble.com/Constraint-to-ensure-value-does-NOT-exist-in-another-table-td4493651.html

I created a function townname_exists (countryfk,regionfk,name), which I
use in conjunction with a check constraint. The constraint operates on
the alias table and the function searches the main table.

The downside is that I need to mirror the logic for both tables and
therefore need two separate functions (one checking town and one
townalias).

--

Best Regards,
Tarlika Elisabeth Schmitz