Re: Use 1/5 primary key from a table as FK of another table...

No, you have to match all of the columns of the primary key. According to the Oracle8 Concepts Manual (pp. 24-12):

  "Foreign keys can consist of multiple columns. However,    a composite foreign key must reference a composite primary    or unique key with the same number of columns and the same    datatypes."

If the first column of the primary key is unique unto itself, you could create a unique key constraint on the parent table containing just that column and reference the new unique key constraint with your foreign key. Beware, however, that you will now have two indexes with the same starting column which could cause some of your queries to use the wrong index for joins.

If it is not unique, then you cannot define a declarative foreign key constraint that references it. What you could do instead is use triggers to enforce this quasi foreign key. You would need an insert/update trigger on the child table to validate against the parent table and an update/delete trigger on the parent table to check the child table for references in the event of change or deletion of the parent key and either disallow the change/delete or propagate it to all child records.

One other possibility is to create a higher level parent table that contains a unique list of valid values from column 1 of your parent.

HTH. Tom Sullivan

In article <39F45A67.D62B8721_at_mcw.edu>,
  Huoy-Jii Khoo <khoo_at_mcw.edu> wrote:
> Hi,
>
> Sorry for the beginner's question.
>
> I was wondering if I could use JUST one out of five primary keys that
 I
> have in table A as the foreign key of another table, let say table B?
>
> Is that possible in Oracle? Is that ok?
>
> Thanks.
> jii
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 27 2000 - 10:48:40 CDT