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

From Gavin Flower
Subject Re: unique across two tables
Date
Msg-id 4E06BB2E.3060903@archidevsys.co.nz
Whole thread Raw
In response to Re: unique across two tables  (Tarlika Elisabeth Schmitz <postgresql4@numerixtechnology.de>)
List pgsql-general
On 23/06/11 23:28, Tarlika Elisabeth Schmitz wrote:
> 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).
>
>
I think ir is safer, and simpler,  to have a flag in one table
indicating the status as reliable or not - rather than have duplicate
logic that is a potential maintenance nightmare.



pgsql-general by date:

Previous
From: Gavin Flower
Date:
Subject: Re: An amusing MySQL weakness--not!
Next
From: Alban Hertroys
Date:
Subject: Re: PostgreSQL 8.4.8 bringing my website down every evening