Hello Gavin, On Wed, 22 Jun 2011 20:53:19 +1200 Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
