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]
