Jeremiah, please send me a dump of the whole table structure in that database. No need to send the actual data. Just CREATE TABLEs.
Also send me an UNEDITED capture of a mysql session where an insert fails in that that error, and which shows that the table really exists in the database. Below it is possible the table really did not exist at the time you got the error. Can you repeat the error manually by trying an insert? Regards, Heikki ----- Original Message ----- From: "Jeremiah Jacks" <[EMAIL PROTECTED]> To: "'Heikki Tuuri'" <[EMAIL PROTECTED]> Sent: Tuesday, July 29, 2003 12:08 AM Subject: RE: Error with foreign key constraint when updating Oh sorry... I was trying to disguise my db name with "mydb", heh. Oh well... There is only one db.. It is tamiyausa. -----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 11:57 AM To: [EMAIL PROTECTED] Subject: Re: Error with foreign key constraint when updating Jeremiah, you are inserting in the database 'tamiyausa' to the table 'product_access_level'. > But the parent table mydb/product does not currently exist! It complains there is no table 'product' in database 'mydb'. Can you do mysql> use tamiyausa mysql> SHOW CREATE TABLE product_access_level; mysql> use mydb mysql> SHOW CREATE TABLE product; ? I am suspecting you printed these from wrong databases. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: ""Jeremiah Jacks"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, July 28, 2003 9:11 PM Subject: Error with foreign key constraint when updating > > I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using > = the pre-compiled binaries. > > I have a database with INNODB tables. > When I insert a row into one of the child tables, I get the following > = MySQL > error: > > INSERT INTO product_access_level (product_id,access_level_id) VALUES > ('10201','2') [nativecode=3D1216 ** Cannot add or update a child row: > a foreign key constraint fails] > > I was not getting this error before with the previous version of > MySQL(3.23.57) that I had installed. > Below is the output of the latest foreign key error from 'SHOW INNODB > STATUS': > I am not sure what the problem is here. In the INNODB STATUS it says = > that my product table doesn't exist?? > Below the status are my table structures. Any input would be helpful. > Thanks! > > ------------------------ > LATEST FOREIGN KEY ERROR > ------------------------ > 030728 13:15:03 Transaction: > TRANSACTION 0 554436, ACTIVE 0 sec, process no 22745, OS thread id = > 864270 insert > ing, thread declared inside InnoDB 500 > 1 lock struct(s), heap size 320 > MySQL thread id 203, query id 11471 localhost root update > INSERT INTO product_access_level (product_id,access_level_id) VALUES > ('10201','2 > ') > Foreign key constraint fails for table tamiyausa/product_access_level: > , > CONSTRAINT `0_281` FOREIGN KEY (`product_id`) REFERENCES `product` = > (`id`) > ON D > ELETE CASCADE > Trying to add to index PRIMARY tuple: > 0: len 5; hex 3130323031; asc 10201;; 1: len 4; hex 00000002; asc = > ....;; 2: > len > 6; hex 0000000875c4; asc ....u.;; 3: len 7; hex 0000000068338b; asc > ....h3.;; > But the parent table mydb/product does not currently exist! > -------------------------------------------------------------------------= > --- > ----- > > > CREATE TABLE `product_access_level` ( > `product_id` varchar(10) NOT NULL default '', `access_level_id` > int(10) unsigned NOT NULL default '0', PRIMARY KEY > (`product_id`,`access_level_id`), KEY `idx_product_id` (`product_id`), > KEY `idx_access_level_id` (`access_level_id`), > CONSTRAINT `0_281` FOREIGN KEY (`product_id`) REFERENCES `product` = > (`id`) ON > DELETE CASCADE, > CONSTRAINT `0_282` FOREIGN KEY (`access_level_id`) REFERENCES = > `access_level` > (access_level_id`) ON DELETE CASCADE) TYPE=3DInnoDB > > CREATE TABLE `product` ( > `id` varchar(10) NOT NULL default '', > `name` varchar(100) NOT NULL default '', > `category_id` int(10) unsigned default NULL, > `retail_value` float unsigned default NULL, > `dealer_price` float unsigned default NULL, `minimum_purchase` int(10) > unsigned default NULL, `case_quantity` int(10) unsigned default NULL, > `status_id` char(2) default NULL, > `description` text, > PRIMARY KEY (`id`), > KEY `idx_category_id` (`category_id`), > KEY `idx_status_id` (`status_id`), > CONSTRAINT `0_274` FOREIGN KEY (`status_id`) REFERENCES `product_status` > (`id`) ON DELETE SET NULL, > CONSTRAINT `0_34` FOREIGN KEY (`category_id`) REFERENCES `category` > (`category_id`) ON DELETE SET NULL) TYPE=3DInnoDB > > CREATE TABLE `access_level` ( > `access_level_id` int(10) unsigned NOT NULL default '0', > `access_level_name` varchar(25) NOT NULL default '', PRIMARY KEY > (`access_level_id`)) TYPE=3DInnoDB > > > -- > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
