Re: "REFERENCES" and UNIQUE

On Wed, 4 Jan 2006, Michelle Konzack wrote:
>
> __( 'stdin' )_________________________________________________________
> /
> | psql:omegasector.sql:125: ERROR: there is no unique constraint matchi
> | ng given keys for referenced table "cities"
> \______________________________________________________________________
>
> and after puzzeling arround what happen, I know it.
>
> __( '/home/michelle.konzack/.pgsql/omegasector.sql' )_________________
> /
> <snip>
> | CREATE TABLE countries (
> | serno int NOT NULL UNIQUE,
> | isocode varchar(2) NOT NULL UNIQUE,
> | EN text NOT NULL,
> | DE text NOT NULL,
> | FR text NOT NULL
> | );
>
> <snip>
>
> | CREATE TABLE cities (
> | serno int NOT NULL UNIQUE,
> | country varchar(2) NOT NULL REFERENCES countries (isocode),
> | EN varchar(30) NOT NULL,
> | DE varchar(30) NOT NULL,
> | FR varchar(30) NOT NULL
> | );
>
> <snip>
>
> | CREATE TABLE members (
> | serno int NOT NULL,
> | version int NOT NULL,
> | dt timestamp NOT NULL,
> | editor int NOT NULL,
> | editor_dt timestamp NOT NULL,
> | subject varchar(40) NOT NULL,
> | sdesc varchar(300) NOT NULL,
> | description text NULL,
> | photos text NOT NULL,
> | timeline text NULL,
> | dossiers text NULL,
> | firstname varchar(30) NOT NULL,
> | middlenames varchar(60) NULL,
> | lastname varchar(30) NOT NULL,
> | fullname varchar(120) NOT NULL,
> | address1 varchar(30) NOT NULL,
> | address2 varchar(30) NULL,
> | street varchar(30) NOT NULL,
> | streetno varchar(6) NULL,
> | city varchar(30) NOT NULL REFERENCES cities (EN),
> | zip varchar(6) NULL,
> | country varchar(2) NOT NULL REFERENCES countries (isocode),
> | telephon varchar(24) NULL,
> | fax varchar(24) NULL,
> | email varchar(60) NOT NULL,
> | url varchar(100) NULL
> | );
> <snip>
> \______________________________________________________________________
>
> OK, it does not work, because "cities.EN" is not UNIQUE. And yes, it
> can not be UNIQUE, because sometimes a cityname exist several times.

Which in this case hints that there's a problem with the schema.

What is the final effect you're looking for? The above does let you
lookup the DE or FR strings for a city of a member (what if two cities had
the same EN name but different DE or FR names, how would you decide which
one it was). If you only wanted to make sure that the city name was in a
list, then it wouldn't matter if you made it unique.

> Is there a solution for this problem?

The easiest is to write triggers that check for a matching row on insert
or update to members and make sure that you aren't removing the last match
on update or deletion of cities. You need to be a little careful to get
the locking right for concurrent actions on cities and members.