Re: "REFERENCES" and UNIQUE - Mailing list pgsql-general

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.