By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,933 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,933 IT Pros & Developers. It's quick & easy.

Error with foreign key constraint when updating

P: n/a

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/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.