473,389 Members | 1,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,389 software developers and data experts.

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

Jul 19 '05 #1
0 2603

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Tetsuji Ueda | last post by:
Hello, I'm currently porting an application using some other DB to MySQL. The database in question has several tables with foreign key relations. The application is written such that, on table...
0
by: Jeremiah Jacks | last post by:
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...
0
by: Morten Gulbrandsen | last post by:
mysql> USE company; Database changed mysql> mysql> DROP TABLE IF EXISTS EMPLOYEE; -------------- DROP TABLE IF EXISTS EMPLOYEE -------------- Query OK, 0 rows affected (0.00 sec)
10
by: Bodza Bodza | last post by:
I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed...
1
by: Jon LaRosa | last post by:
Hi all - I have a web application and I want to be able to do some basic error handling. For example, here is one error I would like to catch and display in a useful way for the user: ...
0
by: Jon LaRosa | last post by:
Hi all - I have a web application and I want to be able to do some basic error handling. For example, here is one error I would like to catch and display in a useful way for the user:...
13
by: dbuchanan | last post by:
Hello, Here is the error message; ---------------------------- Exception Message: ForeignKeyConstraint Lkp_tbl040Cmpt_lkp302SensorType requires the child key values (5) to exist in the...
669
by: Xah Lee | last post by:
in March, i posted a essay “What is Expressiveness in a Computer Language”, archived at: http://xahlee.org/perl-python/what_is_expresiveness.html I was informed then that there is a academic...
15
by: Frank Swarbrick | last post by:
I have the following three tables DROP TABLE CALLTRAK.SERVICE_CODES @ CREATE TABLE CALLTRAK.SERVICE_CODES ( CODE CHAR(1) NOT NULL , CONSTRAINT SERVICE_CODES_PK PRIMARY KEY (CODE) ,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.