Simple add foreign key returns #1215 cannot add foreign key constraint

Question

I am sure I am missing something simple.

RequestLog table:

CREATE TABLE `requestlog` (
 `RequestID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `RequestName` varchar(30) NOT NULL,
 `RequestData` varchar(150) NOT NULL,
 `RequestDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `Version` varchar(15) NOT NULL,
 PRIMARY KEY (`RequestID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

ResponseLog table:

CREATE TABLE `responselog` (
 `ResponseID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `FK_RequestID` int(10) NOT NULL,
 `ResponseText` text NOT NULL,
 PRIMARY KEY (`ResponseID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Trying to add a foreign key on ResponseLog.FK_RequestID with

ALTER TABLE ResponseLog
ADD FOREIGN KEY (FK_RequestID) REFERENCES RequestLog(RequestID)

Don't shoot me, what am I missing?

Answer 1

ALTER TABLE references tables ResponseLog and RequestLog. Your CREATE TABLE statements create tables named requestlog and responselog. Try changing your ALTER TABLE statement so that it uses table identifiers with the same case.

Also, and it is probably the main problem, the referenced fields have different data types. One is an int, the other an unsigned int. Data types have to match, otherwise the fields could become inconsistent. MySQL knows this and prevents you from creating a broken foreign key.