Обсуждение: "REFERENCES" and UNIQUE
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)
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 ;)
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.
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 >
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)
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 >