Error when creating Foreign Key

Chris White · *nix forums beginner Joined: 21 Apr 2006 Posts: 43

On Wednesday 19 July 2006 01:07 pm, Jesse wrote:

Jesse · *nix forums addict Joined: 14 Dec 2005 Posts: 56

Sure. Here is the information on both tables:

DROP TABLE IF EXISTS `karate`.`custtournaments`;
CREATE TABLE `karate`.`custtournaments` (
`ID` int(10) unsigned NOT NULL auto_increment,
`CustID` int(10) unsigned NOT NULL default '0',
`TournamentName` varchar(45) NOT NULL default '',
`TournamentDate` datetime NOT NULL default '0000-00-00 00:00:00',
`Style` varchar(20) default NULL,
`BegLevel` varchar(20) NOT NULL default '',
`EndLevel` varchar(20) NOT NULL default '',
`Match` varchar(20) NOT NULL default '',
`SubDiv` varchar(60) NOT NULL default '',
`BegWt` int(10) unsigned NOT NULL default '0',
`EndWt` int(10) unsigned NOT NULL default '0',
`BegAge` int(10) unsigned NOT NULL default '0',
`EndAge` int(10) unsigned NOT NULL default '0',
`BegHtFt` int(10) unsigned NOT NULL default '0',
`BegHtIn` int(10) unsigned NOT NULL default '0',
`EndHtFt` int(10) unsigned NOT NULL default '0',
`EndHtIn` int(10) unsigned NOT NULL default '0',
`Gender` varchar(1) NOT NULL default '',
`Ring` varchar(2) default NULL,
`Order` int(10) unsigned NOT NULL default '0',
`DivNum` varchar(7) NOT NULL default '',
`Time` varchar( Cool

default NULL,
`Status` varchar(3) NOT NULL default '',
`Judge1` varchar(30) default NULL,
`Judge1Score` double(5,1) default '0.0',
`Judge2` varchar(30) default NULL,
`Judge2Score` double(5,1) default '0.0',
`Judge3` varchar(30) default NULL,
`Judge3Score` double(5,1) default '0.0',
`Judge4` varchar(30) default NULL,
`Judge4Score` double(5,1) default '0.0',
`Judge5` varchar(30) default NULL,
`Judge5Score` double(5,1) default '0.0',
`ScoreKeep` varchar(30) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `karate`.`custpoints`;
CREATE TABLE `karate`.`custpoints` (
`ID` int(11) NOT NULL auto_increment,
`Status` varchar(3) default NULL,
`Association` varchar(15) default NULL,
`Points` tinyint(3) default NULL,
`CTID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `CTID` (`CTID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thanks,
Jesse

----- Original Message -----
From: "Chris White" <chriswhite@interfuel.com>
To: <mysql@lists.mysql.com>
Sent: Wednesday, July 19, 2006 2:33 PM
Subject: Re: Error when creating Foreign Key

Chris White · *nix forums beginner Joined: 21 Apr 2006 Posts: 43

On Wednesday 19 July 2006 10:25 am, Jesse wrote:

Jesse · *nix forums addict Joined: 14 Dec 2005 Posts: 56

Dan Buettner · *nix forums Guru Wannabe Joined: 13 Oct 2005 Posts: 119

Jesse, a stab in the dark here, with some info from the manual.

A foreign key constraint is in My Code erroneously coded,
If MySQL gives the error number 1005 from a CREATE TABLE
statement, and the error message string refers to errno 150, then
the table creation failed because a foreign key constraint was not
correctly formed. Similarly, if an ALTER TABLE fails and it refers
to errno 150, that means a foreign key definition would be incorrectly
formed for the altered table. Starting from version 4.0.13, you can
use SHOW INNODB STATUS to look at a detailed explanation of the
latest InnoDB foreign key error in the server.

You explicitly specify the database of one table with
ALTER TABLE `karate`.`custpoints`
but later you don't explicitly specify the database of the other with:
REFERENCES `custtournaments` (`ID`)

Is it possible you're in the wrong database (different from
'custtournaments') when you issue this command?

Also check out SHOW INNODB STATUS for more info on what might be going on.

HTH,
Dan

On 7/19/06, Jesse <jlc@msdlg.com> wrote:

Jesse · *nix forums addict Joined: 14 Dec 2005 Posts: 56

I'm using MySQL 5 with InnoDB tables. I'm trying to create a foreign key,
but when I try, it's giving me an error. I've tried this with the
MySQLQuery Browser, and EMS SQL Manager 2005. I get the following error in
the Query Browser:

Error while executing query.

ALTER TABLE `karate`.`custpoints` ADD CONSTRAINT `FK_custpoints_1` FOREIGN
KEY `FK_custpoints_1` (`CTID`)
REFERENCES `custtournaments` (`ID`)
ON DELETE CASCADE
ON UPDATE RESTRICT;

MySQL Error Number 1005
Can't create table '.\karate\#sql-304_9.frm' (errno: 150)

Any idea why this is happening, and how to fix it?

Thanks,
Jesse

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Google