MySQL Foreign Key Problem (errno: 150)

[markrd]

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.

[bossbob]

I think I found what the problem is. I think it's the definition of the primary key field.

Try:

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) does not have the UNSIGNED keyword while the foreign key in tblForumMessage (fldForumID) has the UNSIGNED keyword. This keyword is causing the problem - inconsistent type of field. So if you add the UNSIGNED keyword to fldForumID in tblForumTitle:

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


it should work. Give it a try, what have you got to lose?

[markrd]

bossbob. I'm still pretty new at this programing stuff. I tend to miss the little things that are right in front of me. I guess, I'll start seeing some of these mistakes one I have more time in eh? Thanks again, you are awesome.