Re: Foreign Key can't refer to one of 2 primary keys

Reuben D. Budiardja wrote:

>Hi all,
>Suppose I have a table with more than one primary key. If I create another
>table and I want one of the column of that second table REFERENCE to one of
>the primary key of the first table, how do I do that?
>
>eg
>CREATE TABLE test
>(
>col1 VARCHAR(20),
>col2 VARCHAR(20),
>col3 VARCHAR(20),
>PRIMARY KEY (col1,col2,col3)
>);
>
>CREATE TABLE myforeign
>(
>mycol1 VARCHAR(20) REFERENCES test,
>
maybe:

mycol1 VARCHAR(20) REFERENCES test(col1)

But then you will need a unique index or constaint on col1, that would then make the primary key on test redundant with col2 and col3 !

>
>
>I don't se any obvious reason why I cannot do that. Any help?
>
>

Just a guess.