Foreign key question

[Victor Spång Arthursson]

New to foreign keys and have some questions…

The first is, i have a language table with a primary key on the fields
lang and relid:

relid | lang | text
-------+------+-------------
11111 | uk | hello
11111 | dk | hej
11111 | de | guten tag
11112 | uk | world
11112 | dk | værld

In another table, texts, I have the following:

id | text
-------+------+
4 | 11112 |

What I want to do is to create a foreign key between the two tables.
But trying to do so, with the following syntax, I get the following
error message:

ALTER TABLE varer ADD CONSTRAINT varenavn FOREIGN KEY (varenavn) REFERENCES languages(relid) MATCH FULL ON DELETE CASCADE;
ERROR: there is no unique constraint matching given keys for

referenced table "languages"
tostipippitest=#

Can't i create a Foreign key to a field that is not defined as UNIQUE?

And if so, are there any other approach to solve this problem?

Sincerely

Victor

PS If someone has a link to a good tutorial I would love to have it DS

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Terry Lee Tucker]

From the HTML docs:
"The referenced columns must be the columns of a unique or primary key
constraint in the referenced table."

I personally don't know of a work around. Maybe some of the others do.

On Thursday 15 January 2004 10:56 am, Victor Spång Arthursson wrote:

New to foreign keys and have some questions…

The first is, i have a language table with a primary key on the fields
lang and relid:

relid | lang | text
-------+------+-------------
11111 | uk | hello
11111 | dk | hej
11111 | de | guten tag
11112 | uk | world
11112 | dk | værld

In another table, texts, I have the following:

id | text
-------+------+
4 | 11112 |

What I want to do is to create a foreign key between the two tables.
But trying to do so, with the following syntax, I get the following

error message:

> ALTER TABLE varer ADD CONSTRAINT varenavn FOREIGN KEY (varenavn)

REFERENCES languages(relid) MATCH FULL ON DELETE CASCADE;

>ERROR: there is no unique constraint matching given keys for

referenced table "languages"
tostipippitest=#

Can't i create a Foreign key to a field that is not defined as UNIQUE?

And if so, are there any other approach to solve this problem?

Sincerely

Victor

PS If someone has a link to a good tutorial I would love to have it DS

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

--
"The most important reason that people save is the hope of providing a
better life for their children. A society that punishes that impulse
with taxes is foolish. It is draining energy from the single most power-
ful engine of capital accumulation. If the super-rich don't want their
kids to get their money, fine. Donate every penny of it to someone else.
But they are wrong to block others from exercising a free choice."

--Lew Rockwell

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephan Szabo]

On Thu, 15 Jan 2004, [ISO-8859-1] Victor Spång Arthursson wrote:

New to foreign keys and have some questions

The first is, i have a language table with a primary key on the fields
lang and relid:

relid | lang | text
-------+------+-------------
11111 | uk | hello
11111 | dk | hej
11111 | de | guten tag
11112 | uk | world
11112 | dk | værld

In another table, texts, I have the following:

id | text
-------+------+
4 | 11112 |

What I want to do is to create a foreign key between the two tables.
But trying to do so, with the following syntax, I get the following
error message:

> ALTER TABLE varer ADD CONSTRAINT varenavn FOREIGN KEY (varenavn) REFERENCES languages(relid) MATCH FULL ON DELETE CASCADE;

>ERROR: there is no unique constraint matching given keys for

referenced table "languages"

Can't i create a Foreign key to a field that is not defined as UNIQUE?

You are not allowed to do so by the SQL spec, no.
And if so, are there any other approach to solve this problem?

I think you could keep the ids for text blocks in a separate table with
all tables that have such an id keeping a reference to it (thus there's a
separate list of valid ids). Unfortunately depending on the behavior you
want, you may have to write triggers to keep the values straight (for
example if you want the id to go away if all the references in a
particular table go away).

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Terry Lee Tucker <te***@esc1.com> writes:

From the HTML docs:
"The referenced columns must be the columns of a unique or primary key
constraint in the referenced table." I personally don't know of a work around. Maybe some of the others do.

There is no workaround, because foreign keys don't make any sense if
there isn't a uniquely identifiable referenced row.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Martin Marques]

Mensaje citado por Tom Lane <tg*@sss.pgh.pa.us>:

Terry Lee Tucker <te***@esc1.com> writes:

From the HTML docs:
"The referenced columns must be the columns of a unique or primary key
constraint in the referenced table."

I personally don't know of a work around. Maybe some of the others do.

There is no workaround, because foreign keys don't make any sense if
there isn't a uniquely identifiable referenced row.

A better way to understand it is:

Foreign keys are many to one assignments.

Try to think of it as a function (mathematicaly speeking): You can't have an element
from the domain end up on two different elementos of the co-domain.

In simbols:

If f(x) = y and f(x) = z => y = z

Those this bring insight?

P.D.: Aparently you are having problems with your database model.

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
---------------------------------------------------------
Martín Marqués | Programador, DBA
Centro de Telemática | Administrador
Universidad Nacional
del Litoral
---------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Terry Lee Tucker]

On Thursday 15 January 2004 01:34 pm, Martin Marques wrote:

A better way to understand it is:

Foreign keys are many to one assignments.

Many to One is what clicks with me. Thanks...
--

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[James M Moe]

Victor Spång Arthursson wrote:

The first is, i have a language table with a primary key on the fields lang and relid:
relid | lang | text
-------+------+-------------
11111 | uk | hello
11111 | dk | hej
11111 | de | guten tag
11112 | uk | world
11112 | dk | v&aelig;rld

In another table, texts, I have the following:

id | text
-------+------+
4 | 11112 |
Given what you have shown, there is no way foreign keys can work here.
Foreign keys are links between the key values of two tables in a sort
of master/slave relationship.
What I want to do is to create a foreign key between the two tables.

What problem does the concept of a foreign key solve for you?
--
jimoe at sohnen-moe dot com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org