[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.