-2
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),
);

So this is my code here from MySQL and every time I try to run it, only Tables item and copy is created. Table borrow is not created due to "Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'borrow_fk_2' in the referenced table 'copy'".

My search engine is InnoDB.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
OnionCoder
  • 87
  • 7

1 Answers1

1

To reference a compound primary key, you must declare a foreign key with the same number of columns as the primary key, and in the same order. In your case, you need the foreign key to be (isbn, copy_id) like this:

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)
);
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828