FK on a single column referencing a column from co

Question

Not able to create /find the logic to apply FK on a column in child table referencing a column from composite PK of parent table.

create table product(prod_id number,
    prod_name varchar2(20),
    price number,
    constraint PK12 primary key(prod_id,prod_name));

Table created.

create table purchase(prod_id number,
    purchase_price number,
    constraint FK12 foreign key(prod_id) references product(prod_id));
create table purchase(prod_id number,
    purchase_price number,
    constraint FK12 foreign key(prod_id) references product(prod_id))

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

Kinldy suggest how i can incorporate this logic.

Thanks.

Answer 1

You can't.

As the error says there's no matching primary key for that column list; you must have one. You have three options:

1. Remove PROD_NAME from the primary key of PRODUCT. On the face of it this seems like the logical solution, if this is not required in order to make the primary key unique.

2. Add PROD_NAME to the PURCHASE table.

3. Create a unique index on PURCHASE.PROD_ID. This seems excessive if it would be a primary key candidate anyway.

Answer 2

I suspect that this is not unique to Oracle. Considering you have a composite primary key in the referenced table, that implies that only one of the columns comprising the composite key is not enough to uniquely identify the record in that table. Therefore, it's impossible to reference only a single column of the primary key in a foreign key relationship that's one-to-many (e.g. one record in the referenced table can have many records in the referencing table--the one with the FK). However, if the relationship to be established is many-to-many, this may be possible.

HTH.