"REFERENCES" and UNIQUE - Mailing list pgsql-general

Hello,

I get the following error:

  __( '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.

Is there a solution for this problem?

Data for "members" should only accepted if there is a minimum of one
match in "cities.EN" and it must not be UNIQUE.

Thanks and happy new year
Michelle

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSM LinuxMichi
0033/3/88452356    67100 Strasbourg/France   IRC #Debian (irc.icq.com)