为什么当我的外键索引与它被引用的表中的主键匹配时，我有错误代码:1822 ?

Question

create table item
(
isbn varchar(25) not null, 
title varchar(150) not null,
publisher_name varchar(50) not null,
classification_code varchar(10) not null, 
format_type char(2),
constraint item_pk primary key(isbn)
);
create table copy 
(
isbn varchar(25) not null,
copy_id int not null, 
acquired_date not null,
constraint copy_pk primary key(isbn, copy_id),
constraint copy_fk foreign key(isbn) references item(isbn)
);
create table borrow (
isbn varchar(25) not null,
copy_id int not null,
user_id varchar(25) not null,
borrowed_datetime datetime not null,
returned_datetime datetime not null,
constraint borrow_pk primary key (isbn, copy_id, user_id, borrowed_datetime),
constraint borrow_fk_1 foreign key(isbn) references copy(isbn),
constraint borrow_fk_2 foreign key(copy_id) references copy(copy_id),
);

所以这是我的代码这里从MySQL和每次我试图运行它，只有表项和副本被创建。由于"错误代码:1822"，未创建表借用。添加外键约束失败。处理步骤引用表"copy"中约束"borrow_fk_2"缺少索引。

我的搜索引擎是InnoDB

Answer 1

要引用复合主键，必须声明一个列数与主键相同且顺序相同的外键。在您的示例中，需要将外键设置为(isbn, copy_id)，如下所示:

create table borrow (
isbn varchar(25) not null,
copy_id int not null,
user_id varchar(25) not null,
borrowed_datetime datetime not null,
returned_datetime datetime not null,
constraint borrow_pk primary key (isbn, copy_id, user_id, borrowed_datetime),
constraint borrow_fk_1 foreign key(isbn, copy_id) references copy(isbn, copy_id)
);