Getting Error "No matching Unique or Primary key for the Column List", in SQL

Question

drop table reservation;
drop table title_copy;
drop table title;
drop table rental;
drop table member;


create table member(
    member_id number,
    last_name varchar2(25) not null,
    first_name varchar2(25),
    address varchar2(100),
    city varchar2(30),
    phone varchar2(15),
    join_date date DEFAULT SYSDATE not null,
        constraint pk_member primary key(member_id));

commit;

    create table title(
    Title_id number,
    Title varchar2(60) not null,
    Description varchar2(400) not null,
    Rating varchar2(4),
    Category varchar2(20),
    Release_Date date,
        constraint pk_title primary key(Title_id),
        constraint ck_title check(Category = 'Drama''Comedy''Action''Child''Scifi' AND Rating = 'Available''Destroyed''Rented''Reserved'));


commit;

create table title_copy(
    Copy_id number,
    Title_id number,
    Status varchar2(15) not null,
        constraint pk_title_copy primary key(Copy_id,Title_id));


commit;

create table rental(
    Book_Date date,
    Member_id number,
    Copy_id number,
    Act_Ret_Date date,
    Exp_Ret_Date date DEFAULT sysdate+2,
    Title_id number,
        constraint pk_rental primary key(Book_Date,Member_id,Copy_id,Title_id));

commit;


create table reservation(
    Res_Date date,
    Member_id number,
    Title_id number,
        constraint pk_reservation primary key(Res_date,Member_id,Title_id));

commit;     






alter table title_copy  
    add constraint fk_title_copy foreign key(Title_id)
    references title(Title_id);


alter table rental
    add constraint fk_rental2 foreign key(Member_id)
    references member(Member_id);


alter table reservation
    add constraint fk_reservation1 foreign key(Title_id)
    references title(Title_id);


alter table reservation
    add constraint fk_reservation2 foreign key(Member_id)
    references Member(Member_id);


alter table rental
        add constraint fk_rental1 foreign key(Copy_id)
        references title_copy(Copy_id);

alter table rental
        add constraint fk_rental foreign key(Title_id)
        references title_copy(Title_id);





desc member;
desc title;
desc title_copy;
desc rental;
desc reservation;

select constraint_name, constraint_type
from user_constraints
where table_name in ('MEMBER','TITLE','TITLE_COPY','RENTAL','RESERVATION');

I keep getting the error "no matching unique or primary key in column list" and refers too.....

references title_copy(Copy_id);

and

references title_copy(Title_id);

from when I'm trying to set the foreign key constraints....

alter table rental
        add constraint fk_rental1 foreign key(Copy_id)
        references title_copy(Copy_id);

alter table rental
        add constraint fk_rental foreign key(Title_id)
        references title_copy(Title_id);

score 1 · Answer 1 · edited May 23 '17 at 12:04

1

Check this out:

https://stackoverflow.com/a/10809656/4633893

If you primary key is a compound key (as it is in title copy ["primary key(Copy_id,Title_id)"]) then you can't have a foreign key referencing only a part of that compound key (begin edit per comments) unless that field of the compound key is defined as UNIQUE.

edited May 23 '17 at 12:04

Community

1
1

answered Jan 31 '16 at 00:55

Robert Rodkey

423
3
9

score 1 · Accepted Answer · answered Jan 31 '16 at 01:13

You want:

alter table rental
    add constraint fk_rental1 foreign key(Copy_id,Title_id)
    references title_copy(Copy_id,Title_id);

Neither Copy_id nor Title_id is a PK of title_copy. It has a single compound PK {Copy_id,Title_id}.

(If each of those columns is unique in title_copy, declare them so; then you can declare FKs to each individually.)

Getting Error "No matching Unique or Primary key for the Column List", in SQL

2 Answers2