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)