Error when creating Foreign Key
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/[EMAIL PROTECTED]
Re: Error when creating Foreign Key
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 [EMAIL PROTECTED] wrote: 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error when creating Foreign Key
Is it possible you're in the wrong database (different from 'custtournaments') when you issue this command? No. I've tried in SQL Browser, EMS SQL Manager, and MySQL Command line, making sure in each case that I have the karate database selected. I have also re-typed it in MySQL Command line, eliminating the reference to the karate database, and got the same error. I tried the SHOW INNODBSTATUS. I did find something in there that may hint at the problem: 060719 14:07:40 Error in foreign key constraint of table karate/#sql-304_12: FOREIGN KEY FK_custpoints_1 (CTID) REFERENCES custtournaments (ID) ON DELETE CASCADE ON UPDATE RESTRICT: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html for correct foreign key definition. I checked through the page listed above, and my syntax appears to be correct. It appears to be saying that there is no index present for the ID field in the custtournaments table. But, I've double-checked, and there IS an index there. Foreign keys are a little foreign to me at the moment. I understand what they are, and what they do, however, which file to put this on always causes me a little problems. Here's the current relationship as I can explain it: Parent: CustTournaments Children: CustPoints. In other words, for 1 CustTournaments record, there could be multiple CustPoints records. Now, my understanding is that the ForeignKey refers to a parent record in for a Child Database. So, for my purposes, Im saying that if they delete the CustTournaments record, I want it to delete all of the CustPoints record. So, I add a foreign key to the CustPoints table pointing towards it's parent record. I've stored the Parent Key value in the field CTID in the Child table (custpoints), which points to the ID field in the parent record (custtournaments). Is my understanding flawed? Any more light you can shed on this is appreciated. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error when creating Foreign Key
On Wednesday 19 July 2006 10:25 am, Jesse wrote: MySQL Error Number 1005 Can't create table '.\karate\#sql-304_9.frm' (errno: 150) KEY `FK_custpoints_1` (`CTID`) REFERENCES `custtournaments` (`ID`) can you show us the column declarations for these two? Any idea why this is happening, and how to fix it? Thanks, Jesse -- Chris White PHP Programmer/DBlankSlate Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error when creating Foreign Key
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 '-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(8) 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 [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, July 19, 2006 2:33 PM Subject: Re: Error when creating Foreign Key On Wednesday 19 July 2006 10:25 am, Jesse wrote: MySQL Error Number 1005 Can't create table '.\karate\#sql-304_9.frm' (errno: 150) KEY `FK_custpoints_1` (`CTID`) REFERENCES `custtournaments` (`ID`) can you show us the column declarations for these two? Any idea why this is happening, and how to fix it? Thanks, Jesse -- Chris White PHP Programmer/DBlankSlate Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error when creating Foreign Key
On Wednesday 19 July 2006 01:07 pm, Jesse wrote: `ID` int(10) unsigned NOT NULL auto_increment, `CTID` int(11) NOT NULL, the storage types aren't the same. change CTID to INT(10) UNSIGNED NOT NULL and it should work. -- Chris White PHP Programmer/DBank Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Creating foreign key
vvM HI vvM still not working :-( vvM I didn't do the installation , Do we need to set any option vvM specifically during the installation for foreign key to work?? The InnoDB is loaded ? please run show variables like '%innodb%'; and put here. tnks. sql,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 -- $ look into my eyes look: cannot open my eyes - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: Creating foreign key
vinita, Tuesday, November 19, 2002, 2:06:01 AM, you wrote: vvM still not working :-( vvM I didn't do the installation , Do we need to set any option vvM specifically during the installation for foreign key to work?? Check that your both tables are InnoDB: SHOW TABLE STATUS LIKE table_name; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating foreign key
HI Victoria, Harald, Dyego you all were right, the database is not supporting the InnoDb tables; I configure it with --with --innodb option add innodb_data_file_path to the my.cnf. Now it's working!! Thank you so much Vinita sql Dyego Souza do Carmo wrote: vvM HI vvM still not working :-( vvM I didn't do the installation , Do we need to set any option vvM specifically during the installation for foreign key to work?? The InnoDB is loaded ? please run show variables like '%innodb%'; and put here. tnks. sql,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Creating foreign key
HI I'm not sure what I'm doing wrong in the following example, the foreign key is NOT working. I'm using ver3.23.53. - mysql CREATE TABLE unit ( - unitCode CHAR(20) NOT NULL, - unitName CHAR(50), - unitYear CHAR(20) NOT NULL, - PRIMARY KEY (unitCode, unitYear) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE unit_semesters ( - unitCode CHAR(20) NOT NULL, - unitYear CHAR(20) NOT NULL, - semester ENUM(S, 1, 2, Y, R, N) NOT NULL, - PRIMARY KEY (unitCode, unitYear, semester), - INDEX unit_code (unitCode), - FOREIGN KEY (unitCode) REFERENCES room (unitCode) ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO unit (unitCode, unitName, unitYear) - VALUES (433121, testing1, 2000); Query OK, 1 row affected (0.00 sec) ERROR*** I expect the following INSERT to fail since the unitCode(433222) doesn't exist in the table unit BUT mysql INSERT INTO unit_semesters (unitCode, unitYear, semester) - VALUES (433222, 2000, 1); Query OK, 1 row affected (0.00 sec) mysql select * from unit; +--+--+--+ | unitCode | unitName | unitYear | +--+--+--+ | 433121 | testing1 | 2000 | +--+--+--+ 1 row in set (0.00 sec) mysql select * from unit_semesters; +--+--+--+ | unitCode | unitYear | semester | +--+--+--+ | 433222 | 2000 | 1| +--+--+--+ 1 row in set (0.00 sec) mysql Thank you very much vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating foreign key
Change room to unit FOREIGN KEY (unitCode) REFERENCES room (unitCode) ON DELETE CASCADE FOREIGN KEY (unitCode) REFERENCES unit(unitCode) ON DELETE CASCADE Hope this helps, Sil - Original Message - From: vinita vigine Murugiah [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, November 17, 2002 9:15 PM Subject: Creating foreign key HI I'm not sure what I'm doing wrong in the following example, the foreign key is NOT working. I'm using ver3.23.53. -- --- mysql CREATE TABLE unit ( - unitCode CHAR(20) NOT NULL, - unitName CHAR(50), - unitYear CHAR(20) NOT NULL, - PRIMARY KEY (unitCode, unitYear) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE unit_semesters ( - unitCode CHAR(20) NOT NULL, - unitYear CHAR(20) NOT NULL, - semester ENUM(S, 1, 2, Y, R, N) NOT NULL, - PRIMARY KEY (unitCode, unitYear, semester), - INDEX unit_code (unitCode), - FOREIGN KEY (unitCode) REFERENCES room (unitCode) ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO unit (unitCode, unitName, unitYear) - VALUES (433121, testing1, 2000); Query OK, 1 row affected (0.00 sec) ERROR*** I expect the following INSERT to fail since the unitCode(433222) doesn't exist in the table unit BUT mysql INSERT INTO unit_semesters (unitCode, unitYear, semester) - VALUES (433222, 2000, 1); Query OK, 1 row affected (0.00 sec) mysql select * from unit; +--+--+--+ | unitCode | unitName | unitYear | +--+--+--+ | 433121 | testing1 | 2000 | +--+--+--+ 1 row in set (0.00 sec) mysql select * from unit_semesters; +--+--+--+ | unitCode | unitYear | semester | +--+--+--+ | 433222 | 2000 | 1| +--+--+--+ 1 row in set (0.00 sec) mysql Thank you very much vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating foreign key
HI still not working :-( I didn't do the installation , Do we need to set any option specifically during the installation for foreign key to work?? mysql CREATE TABLE unit ( - unitCode CHAR(20) NOT NULL, - unitName CHAR(50), - unitYear CHAR(20) NOT NULL, - PRIMARY KEY (unitCode, unitYear), - INDEX unit_code (unitCode) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE unit_semesters ( - unitCode CHAR(20) NOT NULL, - unitYear CHAR(20) NOT NULL, - semester ENUM(S, 1, 2, Y, R, N) NOT NULL, - PRIMARY KEY (unitCode, unitYear, semester), - INDEX unit_code (unitCode), - FOREIGN KEY (unitCode) REFERENCES unit (unitCode) ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO unit (unitCode, unitName, unitYear) - VALUES (433121, testing1, 2000); Query OK, 1 row affected (0.00 sec) ERROR ** mysql INSERT INTO unit_semesters (unitCode, unitYear, semester) - VALUES (433222, 2000, 1); Query OK, 1 row affected (0.00 sec) Silmara wrote: Change room to unit FOREIGN KEY (unitCode) REFERENCES room (unitCode) ON DELETE CASCADE FOREIGN KEY (unitCode) REFERENCES unit(unitCode) ON DELETE CASCADE Hope this helps, Sil - Original Message - From: vinita vigine Murugiah [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, November 17, 2002 9:15 PM Subject: Creating foreign key HI I'm not sure what I'm doing wrong in the following example, the foreign key is NOT working. I'm using ver3.23.53. -- --- mysql CREATE TABLE unit ( - unitCode CHAR(20) NOT NULL, - unitName CHAR(50), - unitYear CHAR(20) NOT NULL, - PRIMARY KEY (unitCode, unitYear) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE unit_semesters ( - unitCode CHAR(20) NOT NULL, - unitYear CHAR(20) NOT NULL, - semester ENUM(S, 1, 2, Y, R, N) NOT NULL, - PRIMARY KEY (unitCode, unitYear, semester), - INDEX unit_code (unitCode), - FOREIGN KEY (unitCode) REFERENCES room (unitCode) ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO unit (unitCode, unitName, unitYear) - VALUES (433121, testing1, 2000); Query OK, 1 row affected (0.00 sec) ERROR*** I expect the following INSERT to fail since the unitCode(433222) doesn't exist in the table unit BUT mysql INSERT INTO unit_semesters (unitCode, unitYear, semester) - VALUES (433222, 2000, 1); Query OK, 1 row affected (0.00 sec) mysql select * from unit; +--+--+--+ | unitCode | unitName | unitYear | +--+--+--+ | 433121 | testing1 | 2000 | +--+--+--+ 1 row in set (0.00 sec) mysql select * from unit_semesters; +--+--+--+ | unitCode | unitYear | semester | +--+--+--+ | 433222 | 2000 | 1| +--+--+--+ 1 row in set (0.00 sec) mysql Thank you very much vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
