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

From Tarlika Elisabeth Schmitz
Subject Re: unique across two tables
Date
Msg-id 20110623122838.3c79dd16@dick.coachhouse
Whole thread Raw
In response to Re: unique across two tables  (Gavin Flower <GavinFlower@archidevsys.co.nz>)
Responses Re: unique across two tables
List pgsql-general
Hello Gavin,

On Wed, 22 Jun 2011 20:53:19 +1200
Gavin Flower <GavinFlower@archidevsys.co.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

pgsql-general by date:

Previous
From: Thom Brown
Date:
Subject: Re: autovacuum ignores some tables
Next
From: Aritz Dávila
Date:
Subject: Remote connection issues