Re: "REFERENCES" and UNIQUE

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)