Thread: Referencing multiple primary keys from a single table.

Referencing multiple primary keys from a single table.

From
Daniel Staal
Date:
What is the best way to reference (use as foreign key) a table with
multiple primary keys?

Here's what I have:
Table1:
field1
field2
field3
...
primary key (field1, field2, field3)

Then I have another table that needs to reference Table1 with a
foreign key.  What I really want to do is:
Table2:
field1 Primary key
field2 references table1
...

But that doesn't work.  I haven't yet tried the option of giving
Table2 all three fields of Table1, partly because I'm not really sure
how to write that...  (I did try linking it to the implicit field
generated when I created Table1.  Didn't work.)  The option I'm sure
would work is shoving all three key fields of Table1 into one field,
but that's a bit messy with the data.  (Though it is doable.)

Is there any really good way to do this that I'm just missing?

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

Re: Referencing multiple primary keys from a single table.

From
Stephan Szabo
Date:
On Mon, 22 Sep 2003, Daniel Staal wrote:

>
> What is the best way to reference (use as foreign key) a table with
> multiple primary keys?
>
> Here's what I have:
> Table1:
> field1
> field2
> field3
> ...
> primary key (field1, field2, field3)

This isn't multiple primary keys, it's a single primary key with
3 columns.

> Then I have another table that needs to reference Table1 with a
> foreign key.  What I really want to do is:
> Table2:
> field1 Primary key
> field2 references table1

What are you actually trying to do here?  field2 is a single column
and you're referencing a 3 column key, so I'm not sure what behavior
you're looking for.

Re: Referencing multiple primary keys from a single

From
Daniel Staal
Date:
--On Monday, September 22, 2003 15:05 -0700 Stephan Szabo
<sszabo@megazone.bigpanda.com> wrote:


> This isn't multiple primary keys, it's a single primary key with
> 3 columns.

Ah, I wasn't sure how to refer to it.

>> Then I have another table that needs to reference Table1 with a
>> foreign key.  What I really want to do is:
>> Table2:
>> field1 Primary key
>> field2 references table1
>
> What are you actually trying to do here?  field2 is a single column
> and you're referencing a 3 column key, so I'm not sure what behavior
> you're looking for.

Simple: I'm looking for a way to use one field in Table2 to refer to
one row in Table1.  (Instead of using 3.)

I think you've actually answered my question though.  The tone of the
response made me realize I was thinking like my program which
interchanges field1.T1:field2.T1:field3.T1 with other readings of the
serial number...  (In other words: I'm trying to do something which
is a dumb idea in the first place.  The best thing to do is not to do
it.)

Thanks for giving my brain the jolt it needed.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

Re: Referencing multiple primary keys from a single table.

From
Bruno Wolff III
Date:
On Mon, Sep 22, 2003 at 16:34:11 -0500,
  Daniel Staal <DStaal@usa.net> wrote:
>
> What is the best way to reference (use as foreign key) a table with
> multiple primary keys?
>
> Here's what I have:
> Table1:
> field1
> field2
> field3
> ...
> primary key (field1, field2, field3)
>
> Then I have another table that needs to reference Table1 with a
> foreign key.  What I really want to do is:
> Table2:
> field1 Primary key
> field2 references table1
> ...
>
> But that doesn't work.  I haven't yet tried the option of giving
> Table2 all three fields of Table1, partly because I'm not really sure
> how to write that...  (I did try linking it to the implicit field
> generated when I created Table1.  Didn't work.)  The option I'm sure
> would work is shoving all three key fields of Table1 into one field,
> but that's a bit messy with the data.  (Though it is doable.)

If table2 looked like this
field1 Primary Key
field2 - field4 correspond to field1 - field3 of table 1
then you want to use a foreign key constraint after the column declarations.
Something like:
foreign key (field2, field3, field4) references table1

Re: Referencing multiple primary keys from a single table.

From
Bruno LEVEQUE
Date:
Why do you not use a sequence number like index ?
Something like :
create table tale1 {
seque serial,
field1,
field2,
field3,
...
primary key (field1, field2, field3)
)

create tabla table2 {
field4,
...
seque integer,
...)

with table1.seque = table2.seque


Bruno


Daniel Staal wrote:

>
> What is the best way to reference (use as foreign key) a table with
> multiple primary keys?
>
> Here's what I have:
> Table1:
> field1
> field2
> field3
> ...
> primary key (field1, field2, field3)
>
> Then I have another table that needs to reference Table1 with a
> foreign key.  What I really want to do is:
> Table2:
> field1 Primary key
> field2 references table1
> ...
>
> But that doesn't work.  I haven't yet tried the option of giving
> Table2 all three fields of Table1, partly because I'm not really sure
> how to write that...  (I did try linking it to the implicit field
> generated when I created Table1.  Didn't work.)  The option I'm sure
> would work is shoving all three key fields of Table1 into one field,
> but that's a bit messy with the data.  (Though it is doable.)
>
> Is there any really good way to do this that I'm just missing?
>
> Daniel T. Staal
>
> ---------------------------------------------------------------
> This email copyright the author.  Unless otherwise noted, you
> are expressly allowed to retransmit, quote, or otherwise use
> the contents for non-commercial purposes.  This copyright will
> expire 5 years after the author's death, or in 30 years,
> whichever is longer, unless such a period is in excess of
> local copyright law.
> ---------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com