Re: "REFERENCES" and UNIQUE

On 1/4/06, Michelle Konzack <linux4michelle(at)freenet(dot)de> 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 ;)