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=1216 ** 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=InnoDB
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=InnoDB
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=InnoDB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
