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');
我不断收到错误"列列表中没有匹配的唯一键或主键",并且也引用.....
references title_copy(Copy_id);
和
references title_copy(Title_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);
你需要:
alter table rental
add constraint fk_rental1 foreign key(Copy_id,Title_id)
references title_copy(Copy_id,Title_id);
Copy_id和Title_id都不是title_copy的PK。它有一个单一的复合PK {Copy_id,Title_id}。
(如果这些列中的每一列在title_copy中都是唯一的,请声明它们;然后您可以单独向每个列声明 FK。
看看这个:
https://stackoverflow.com/a/10809656/4633893
如果主键是复合键(如标题副本 ["主键(Copy_id,Title_id)"]),则不能让外键仅引用该复合键的一部分(根据注释开始编辑),除非复合键的该字段定义为 UNIQUE。