MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMySQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old April 8th, 2004, 07:36 PM
crudesys crudesys is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2004
Location: California
Posts: 25 crudesys User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question 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.

Reply With Quote
  #2  
Old April 16th, 2004, 07:20 PM
crudesys crudesys is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2004
Location: California
Posts: 25 crudesys User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Lightbulb

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

Reply With Quote
  #3  
Old February 8th, 2009, 04:38 PM
bugmehnot bugmehnot is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2009
Posts: 5 bugmehnot User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 51 m 26 sec
Reputation Power: 0
Thank You!

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

Reply With Quote
  #4  
Old June 18th, 2009, 11:35 AM
bdrhoa bdrhoa is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2009
Posts: 1 bdrhoa User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 m 44 sec
Reputation Power: 0
Another Cause Of This Problem - misisng index

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.

Last edited by bdrhoa : June 18th, 2009 at 11:36 AM. Reason: correct grammur

Reply With Quote
  #5  
Old January 14th, 2010, 10:58 AM
malvikm malvikm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2010
Posts: 2 malvikm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 32 m 53 sec
Reputation Power: 0
Table Engines Matter

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

Reply With Quote
  #6  
Old November 9th, 2011, 09:28 AM
jeeka321 jeeka321 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2011
Posts: 1 jeeka321 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 15 m 24 sec
Reputation Power: 0
Angry Helllp

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.

Reply With Quote
  #7  
Old January 8th, 2013, 05:13 AM
akahgy akahgy is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2013
Posts: 1 akahgy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 m 29 sec
Reputation Power: 0
Fixing error nr 150

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/

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMySQL Development > MySQL Foreign Key Problem (errno: 150)


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap