Re: foreign key related

From: <markp7832_at_my-deja.com>
Date: Mon, 29 Nov 1999 20:28:28 GMT
Message-ID: <81unl6$2fv$1@nnrp1.deja.com>

In article <384286E2.C01BEC49_at_questone.com>,   parora_at_questone.com wrote:
> Hi all
>
> I am stuck up in a very small problem
>
> if i have got a combined primary key of 3 fields(f1 f2 f3) in one
table
> (lets say tab1)
> and in another table (lets say tab2(field1,field2) ) i want one of the
> fields (from tab1) as the foreign key here in tab2 (lets say field1
> references tab1(f1) )...
>
> is it possible because f1 is not the primary key its a part of the
> primary key of table tab1
>
> hope i m clear with the problem
>

It would appear the answer is no, however, you can reference a multiple column key as a foreign key as long as you have multiple matching columns in the foreign key. Note this is ver 7.3.3.5

OPS2> create table marktest1 ( fld1 varchar2(10), fld2 varchar2(10) );

Table created.

OPS2> alter table marktest1 add constraint marktest1_pk primary key (fld1,fld2);

Table altered.

OPS2> create table marktest2 (fld1 varchar2(10), fld3 varchar2(15));

Table created.

OPS2> alter table marktest2 add constraint marktestfk foreign key (fld1) references marktest1 (fld1);
alter table marktest2 add constraint marktestfk foreign key (fld1) references marktest1 (fld1)

                 *

ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

  1 alter table marktest2 add constraint marktestfk   2* foreign key (fld1, fld3) references marktest1 (fld1,fld2) OPS2> / Table altered.

I hope this helps.
--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 29 1999 - 14:28:28 CST