|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
|
|||
|
|||
|
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. |
|
#2
|
|||
|
|||
|
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 |
![]() |
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > MySQL Foreign Key Problem (errno: 150) |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|