MySQL Foreign Key Problem (errno: 150)

Hi, i have a problem with creating a foreign key in tblForumMessage.
here's how i created the tables:


CREATE TABLE tblMember (
fldMemberID VARCHAR(15) UNIQUE NOT NULL DEFAULT '',
........ more fields ......
PRIMARY KEY (fldMemberID),
........ indices here ......
) TYPE=INNODB;


CREATE TABLE tblForumTitle (
fldForumID SMALLINT(5) UNIQUE NOT NULL AUTO_INCREMENT,
........ more fields ......
PRIMARY KEY (fldForumID),
INDEX indByMemberID (fldByMemberID),
FOREIGN KEY (fldByMemberID) REFERENCES tblMember(fldMemberID)
ON DELETE CASCADE
) TYPE=INNODB;


CREATE TABLE tblForumMessage (
fldMessage TEXT NOT NULL,
fldMemberID VARCHAR(15) NOT NULL DEFAULT '',
fldForumID SMALLINT(5) UNSIGNED NOT NULL,
fldPostDate DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
INDEX indPostDate (fldPostDate),
INDEX indForumMbrID (fldMemberID),
INDEX indForumMsgID (fldForumID),
FOREIGN KEY (fldMemberID) REFERENCES tblMember(fldMemberID)
ON DELETE CASCADE,
FOREIGN KEY (fldForumID) REFERENCES tblForumTitle(fldForumID)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=INNODB;

the error i get is "cant create tblForumMessage.frm (errno:150)" which says there's a problem with my foreign key definition.

if i take out the last foreign key in tblForumMessage, it works.

CREATE TABLE tblForumMessage (
fldMessage TEXT NOT NULL,
fldMemberID VARCHAR(15) NOT NULL DEFAULT '',
fldForumID SMALLINT(5) UNSIGNED NOT NULL,
fldPostDate DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
INDEX indPostDate (fldPostDate),
INDEX indForumMbrID (fldMemberID),
INDEX indForumMsgID (fldForumID),
FOREIGN KEY (fldMemberID) REFERENCES tblMember(fldMemberID)
ON DELETE CASCADE
) TYPE=INNODB;

after this worked, i tried to alter the table to add the foreign key
ALTER TABLE tblForumMessage ADD FOREIGN KEY (fldForumID) REFERENCES tblForumTitle(fldForumID)
but it still gives me the errno:150


something tells me that the problem lies within my definition of:
FOREIGN KEY (fldForumID) REFERENCES tblForumTitle(fldForumID) ON DELETE CASCADE ON UPDATE CASCADE

can someone help me with this problem?
your help is greatly appreciated. thanks.

Finally. I found out what the problem was. It wasn't about the definition of the foreign key. actually it was the definition of the primary key field.

CREATE TABLE tblForumTitle (
fldForumID SMALLINT(5) UNIQUE NOT NULL AUTO_INCREMENT,
......


CREATE TABLE tblForumMessage (
fldMessage TEXT NOT NULL,
fldMemberID VARCHAR(15) NOT NULL DEFAULT '',
fldForumID SMALLINT(5) UNSIGNED NOT NULL,
......


the primary key in tblForumTitle (fldForumID) did not have the UNSIGNED keyword while the foreign key in tblForumMessage (fldForumID) had the UNSIGNED keyword. This keyword was causing the problem - inconsistent type of field. so i added the UNSIGNED keyword to fldForumID in tblForumTitle:

CREATE TABLE tblForumTitle (
fldForumID SMALLINT(5) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
......


just a single detail left out made life miserable. have a great day everyone

crudesys - I created an account ESPECIALLY to thank you for this solution. It was driving me absolutely mental!

I had the same problem, but it turns out it was for a different reason.

I had a unique index on key1+key2. I was able to make the foreign key constraint on table1.key1 with no problem. But I got the error on table2.key2.

I had to add a separate index on key2 to solve the problem.

I was facing the same problem and did everything said in here by matching the 2 column types.

Then I realized that my referenced table engine is MyISAM. This table engine does not support Foreign Key Constraints. I had to change that to InnoDB and it worked.

However InnoDB does not support full text search.

Its heaven and hell huh

HI GUYS !

I'm having a problem with this FK issues for three days now i couldnt solve, neither could i allow my mind enjoy peace.

My own problem does not STOP the table from CREATION, But rather it accept the FOREIGN KEY AS (MULL), something i never heard . guys Help ... Please...i have two tables

CREATE TABLE LOGIN{
userid INT(11) NOT NULL auto_increment,
........
PRIMARY KEY (userid)

}ENGINE =INNODB;

THe seocnd Table

CREATE TABLE comment(
commentid INT(11) NOT NULL
FK_userid INT(11) NOT NULL,
......
PRIMARY KEY (commentid),
FOREIGN KEY (FK_userid) REFERENCES login(userid)

}ENGINE=INNODB;

PROBLEM

Guys the table works ,BUt shows FK_userid as (MULL ), instead of FOREIGN KEY as wanted. Argh! I tried alot but didnt succeed . Please your help is appreciated.

The issue is of course related to FKs.
You have to eliminate them, the easiest way is to "ALTER TABLE table_name DROP FOREIGN KEY fk_column".
Reference: justbugs.wordpress.com/2013/01/08/fixing-sql-errno-150/