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