Re: "REFERENCES" and UNIQUE

On 1/4/06, Michelle Konzack <linux4michelle@freenet.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 ;)