On 1/4/06, Michelle Konzack <linux4michelle@xxxxxxxxxx> wrote: > 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. > you can use a before insert trigger to confirm if there is a match... return null to avoid insertion > 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) > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
