MySQL - complete newbie - what's wrong with my foreign key coding?

Question

When trying to create the foreign keys on the last table I get the error "cannot add foreign key constraint" -

create database library_PW;
use library_PW;

create table title(
title_id    varchar(20)primary key,
name        varchar(50)not null,
reservation_no  numeric(10),
lending_time    varchar(15));

create table item(
title_id    varchar(20)not null,
item_id     varchar(20)not null,
constraint      pk_item primary key(title_id,item_id));

create table magazine(
mag_id      varchar(20)not null,
mag_date    varchar(15)not null,
constraint      pk_magazine primary key(mag_id,mag_date)); 

create table book(
ISBN        varchar(20)primary key,
date_added      date not null);

create table author(
author_id   varchar(20)primary key,
author_name varchar(30)not null);   

create table book_author(
ISBN        varchar(20),
author_id   varchar(20),
index       (ISBN),
index       (author_id),
constraint  pk_book_author primary key(ISBN,author_id),
constraint  fk_ISBNCode foreign key (ISBN) references book(ISBN),
constraint  fk_authorcode foreign key (author_id) references author(author_id));

create table borrower(
membership_id   varchar(20)primary key,
name        varchar(20)not null,
address     varchar(60)not null,
dob     date not null,
date_joined date not null,
telephone   numeric(12),
email       varchar(30));

create table reservation(
title_id    varchar(20),
membership_id   varchar(20),
reserve_date    varchar(20),
index       (title_id),
index       (membership_id),
constraint  pk_reservation primary key(title_id, membership_id,reserve_date),
constraint  fk_title foreign key(title_id) references title(title_id),
constraint  fk_mem_id foreign key(membership_id) references borrower(membership_id));

create table loan(
title_id    varchar(20),
item_id     varchar(20),
borrower_date   varchar(20),
index       (title_id),
index       (item_id), 
constraint  pk_reservation primary key(title_id,item_id,borrower_date),
constraint  fk_loantitle foreign key(title_id) references title(title_id),
constraint  fk_loanitem foreign key(item_id) references item(item_id));

Thanks in advance!

Answer 1

When I run into this issue, its always because I choose the wrong type data type.. The data type for the child column must match the parent column exactly.

Example: table.key = int & table.child=vchar

For me, its always that! Hope that helps.

Answer 2

Thanks all! It wasn't the data type (for once), it was the problem with the table item as suggested, and SHOW ENGINE INNODB STATUS\G pointed me to the answer.

All that was wrong was in the table item I should have had item_id before title_id.

Thanks again.