Thread: Foreign key question

Foreign key question

From

Victor Spång Arthursson

Date:

15 January 2004, 14:57:27

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

Re: Foreign key question

From

Terry Lee Tucker

Date:

15 January 2004, 15:28:18

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 majordomo@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: terry@esc1.com

Re: Foreign key question

From

Stephan Szabo

Date:

15 January 2004, 15:38:04

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

Re: Foreign key question

From

Tom Lane

Date:

15 January 2004, 15:38:38

Terry Lee Tucker <terry@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

Re: Foreign key question

From

Martin Marques

Date:

15 January 2004, 17:41:05

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

> Terry Lee Tucker <terry@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
---------------------------------------------------------

Re: Foreign key question

From

Terry Lee Tucker

Date:

15 January 2004, 17:52:08

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: terry@esc1.com

Re: Foreign key question

From

James M Moe

Date:

15 January 2004, 20:28:12

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