Thread: "REFERENCES" and UNIQUE

"REFERENCES" and UNIQUE

From
Michelle Konzack
Date:
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)

Re: "REFERENCES" and UNIQUE

From
Jaime Casanova
Date:
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 ;)

Re: "REFERENCES" and UNIQUE

From
Stephan Szabo
Date:
On Wed, 4 Jan 2006, Michelle Konzack wrote:
>
>   __( '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.

Which in this case hints that there's a problem with the schema.

What is the final effect you're looking for?  The above does let you
lookup the DE or FR strings for a city of a member (what if two cities had
the same EN name but different DE or FR names, how would you decide which
one it was). If you only wanted to make sure that the city name was in a
list, then it wouldn't matter if you made it unique.

> Is there a solution for this problem?

The easiest is to write triggers that check for a matching row on insert
or update to members and make sure that you aren't removing the last match
on update or deletion of cities. You need to be a little careful to get
the locking right for concurrent actions on cities and members.

Re: "REFERENCES" and UNIQUE

From
Stephan Szabo
Date:
On Wed, 4 Jan 2006, Stephan Szabo wrote:

> Which in this case hints that there's a problem with the schema.
>
> What is the final effect you're looking for?  The above does let you

Err, that should be doesn't.

> lookup the DE or FR strings for a city of a member (what if two cities had
> the same EN name but different DE or FR names, how would you decide which
> one it was). If you only wanted to make sure that the city name was in a
> list, then it wouldn't matter if you made it unique.
>
> > Is there a solution for this problem?
>
> The easiest is to write triggers that check for a matching row on insert
> or update to members and make sure that you aren't removing the last match
> on update or deletion of cities. You need to be a little careful to get
> the locking right for concurrent actions on cities and members.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Re: "REFERENCES" and UNIQUE

From
Michelle Konzack
Date:
Am 2006-01-04 12:08:30, schrieb Stephan Szabo:
>
> On Wed, 4 Jan 2006, Michelle Konzack wrote:

> > | 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
> > |     );

> > | 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
> > |     );

> Which in this case hints that there's a problem with the schema.
>
> What is the final effect you're looking for?  The above does let you
> lookup the DE or FR strings for a city of a member (what if two cities had
> the same EN name but different DE or FR names, how would you decide which
> one it was). If you only wanted to make sure that the city name was in a
> list, then it wouldn't matter if you made it unique.

Hmmm, never seen this.  I have checked my table and I have more
then 30.000 cities and no doubles.

What do you think, should I do?

First I was thinking, I use additonaly geographical coordinates,
but because I have none I have leaved them out.  Maybe I should
use it even if I do not know it currently?

> The easiest is to write triggers that check for a matching row on insert
> or update to members and make sure that you aren't removing the last match
> on update or deletion of cities. You need to be a little careful to get
> the locking right for concurrent actions on cities and members.

Hmmm...

I think, I will change this part.

If I enter new members and I add the city, I will do following

    1)  enter ISO countrycode
    2)  klick in a link to select the first letter of the city
        which triger a query on the availlabele cities.
    3)  now     a)  select city from the table
           or   b)  enter a new cityname
    4)  while submiting the new/changed member data I use only
        the "serno" o determine which city I have choosen if
        several cities of the same name exist

This mean, I should change the TABLE to

CREATE TABLE cities (
    serno       int             NOT NULL UNIQUE,
    country     varchar(2)      NOT NULL REFERENCES countries (isocode),
    geo         ???,
    EN          varchar(30)     NOT NULL,
    DE          varchar(30),
    FR          varchar(30)
    );

Is there a data type for geographical data like GIS or something similar?

Please note, that I am using PostgreSQL 7.4.5

Greetings
    Michelle Konzack
    Systemadministrator
    Tamay Dogan Network
    Debian GNU/Linux Consultant


--
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)

Re: "REFERENCES" and UNIQUE

From
Stephan Szabo
Date:
On Tue, 17 Jan 2006, Michelle Konzack wrote:

> Am 2006-01-04 12:08:30, schrieb Stephan Szabo:
> >
> > On Wed, 4 Jan 2006, Michelle Konzack wrote:
>
> > > | 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
> > > |     );
>
> > > | 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
> > > |     );
>
> > Which in this case hints that there's a problem with the schema.
> >
> > What is the final effect you're looking for?  The above does let you
> > lookup the DE or FR strings for a city of a member (what if two cities had
> > the same EN name but different DE or FR names, how would you decide which
> > one it was). If you only wanted to make sure that the city name was in a
> > list, then it wouldn't matter if you made it unique.
>
> Hmmm, never seen this.  I have checked my table and I have more
> then 30.000 cities and no doubles.

It really depends on what an entry in cities is meant to mean.  There are
many duplicate city names in the US which theoretically are different
cities but could probably potentially be the same entry if all the other
data is the same.

With a little searching, I found a Paris, Tennessee for example.

> What do you think, should I do?

This depends on the business rules, but here are some possibilities.

If it's assumed that all cities in the same country are the same row (ie,
they all have the same strings and other data associated), then
potentially the EN and country are as a pair unique and can be referenced.

If it's assumed that all cities with a given name have the same associated
data, then EN is unique and can be referenced.  Note, from the above I
would assume this is not true if cities references a country.

The safest would be something like the below, however, alot of people may
not know a useful geographical coordinate (for example long/lat) for where
they actually are.

> First I was thinking, I use additonaly geographical coordinates,
> but because I have none I have leaved them out.  Maybe I should
> use it even if I do not know it currently?
>
> > The easiest is to write triggers that check for a matching row on insert
> > or update to members and make sure that you aren't removing the last match
> > on update or deletion of cities. You need to be a little careful to get
> > the locking right for concurrent actions on cities and members.
>
> Hmmm...
>
> I think, I will change this part.
>
> If I enter new members and I add the city, I will do following
>
>     1)  enter ISO countrycode
>     2)  klick in a link to select the first letter of the city
>         which triger a query on the availlabele cities.
>     3)  now     a)  select city from the table
>            or   b)  enter a new cityname
>     4)  while submiting the new/changed member data I use only
>         the "serno" o determine which city I have choosen if
>         several cities of the same name exist
>
> This mean, I should change the TABLE to
>
> CREATE TABLE cities (
>     serno       int             NOT NULL UNIQUE,
>     country     varchar(2)      NOT NULL REFERENCES countries (isocode),
>     geo         ???,
>     EN          varchar(30)     NOT NULL,
>     DE          varchar(30),
>     FR          varchar(30)
>     );
>
> Is there a data type for geographical data like GIS or something similar?
>
> Please note, that I am using PostgreSQL 7.4.5
>
> Greetings
>     Michelle Konzack
>     Systemadministrator
>     Tamay Dogan Network
>     Debian GNU/Linux Consultant
>
>
> --
> 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
>