Thread: Referencing multiple primary keys from a single table.
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. ---------------------------------------------------------------
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.
--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. ---------------------------------------------------------------
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
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