"REFERENCES" and UNIQUE

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)