Hello all,
I have the following 2 tables:
create table custinfo(
cust_custno number(5),
cust_plantno number(3),
cust_name varchar2(50));
alter table custinfo add constraint pk_custinfo_custno_plantno primary key (cust_custno, cust_plantno);
create table contracts(
cont_custno number(5),
cont_name varchar2(30),
cont_start date,
cont_end date,
cont_notes varchar2(50));
alter table contracts add constraint fk_contracts_custno foreign key (cont_custno) references custinfo (cust_custno);
When I execute the above statements, it gives me an error:
SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
gives a column-list for which there is no matching unique or primary
key constraint in the referenced table.
*Action: Find the correct column names using the ALL_CONS_COLUMNS
catalog view
It is my understanding, that this is happening because the PK in the first table is a composite PK, and the FK in the second table is trying to use only one of the two columns making the PK.
But I don't need the second column in my FK, in fact, it is imperative that I not use the second column (cust_plantno), but use the first one (cust_custno).
Is there a solution to this situation?
Thank you.