Hi!
Please send your postings to [EMAIL PROTECTED] The newsgroup
mailing.database.mysql is only a mirror of that mailing list.
The error message below is misleading. It should really be 'Cannot update a
parent row...'. Internally InnoDB does the update by deleting and
reinserting the index record. That is the logic behind the misleading error
message 'Cannot delete a parent row...'.
There is also a bug related to this. Below you see that also an update of
the PRIMARY KEY fails, because it internally means deleting and reinserting
the referenced secondary index record. I have fixed that bug in upcoming
4.0.7. In 4.0.7 only updating the columns mentioned in the foreign key
constraint can generate a foreign key constraint error. These bug fixes will
be backported to 3.23.xx only if the bugs cause problems to users of 3.23.
Good news: in 4.0.7 you can declare
FOREIGN KEY (...) REFERENCES ... (...) ON UPDATE CASCADE
That should solve your problem if you want to update the referenced key in
the parent row. That will make InnoDB automatically update also the child
rows.
Regards,
Heikki
sql query
...............
Tested on Linux, 4.0.6:
mysql> create database despatches_inno;
Query OK, 1 row affected (0.02 sec)
mysql> use despatches_inno
Database changed
mysql> CREATE TABLE pickup_points (
-> PP_RecordId int(11) NOT NULL auto_increment,
-> PP_AffId int(11) default NULL,
-> PP_Code varchar(10) NOT NULL default '',
-> PP_CompanyName varchar(40) NOT NULL default '',
-> PP_BranchName varchar(40) NOT NULL default '',
-> PP_Contact varchar(40) default NULL,
-> PP_Address1 varchar(40) NOT NULL default '',
-> PP_Address2 varchar(40) default NULL,
-> PP_Address3 varchar(40) default NULL,
-> PP_PostCode varchar(40) default NULL,
-> PP_Town varchar(40) NOT NULL default '',
-> PP_County varchar(40) default NULL,
-> PP_Country varchar(40) NOT NULL default '',
-> PP_Tel1 varchar(40) default NULL,
-> PP_Tel2 varchar(40) default NULL,
-> PP_Fax varchar(40) default NULL,
-> PP_Email varchar(40) default NULL,
-> PP_Locality varchar(40) default NULL,
-> PP_Issuer varchar(40) default NULL,
-> PP_AffCode varchar(10) default NULL,
-> PP_Status varchar(10) default 'ACTIVE',
-> PP_StdCharge varchar(10) default NULL,
-> PRIMARY KEY (PP_RecordId),
-> UNIQUE KEY PP_Code (PP_Code),
-> KEY PP_StdCharge (PP_StdCharge)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> --
mysql> -- Table structure for table 'towns_pickup_points'
mysql> --
mysql>
mysql> CREATE TABLE towns_pickup_points (
-> TPP_TownId int(11) NOT NULL default '0',
-> TPP_PPCode varchar(10) NOT NULL default '',
-> TPP_Default char(1) default 'Z',
-> TPP_RecordId int(11) NOT NULL auto_increment,
-> PRIMARY KEY (TPP_RecordId),
-> UNIQUE KEY TPP_RecordId (TPP_RecordId),
-> KEY TPP_TownId (TPP_TownId),
-> KEY TPP_PPCode (TPP_PPCode),
-> FOREIGN KEY (`TPP_PPCode`) REFERENCES
-> `despatches_inno.pickup_points` (`PP_Code`),
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into pickup_points(PP_Code) values ('abc');
Query OK, 1 row affected (0.07 sec)
mysql> insert into towns_pickup_points(TPP_PPCode) values ('abc');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql>
mysql> update pickup_points set PP_Status = 'abbaguu';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update pickup_points set PP_Code = 'frobboz';
ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
mysql>
mysql> update pickup_points set PP_RecordId = 10;
ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
mysql>
.......................
From: My Deja ([EMAIL PROTECTED])
Subject: InnoDB foreign key problems - is Heikki out there?
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.mysql
Date: 2002-12-17 16:13:36 PST
I have this problem with foreign keys.
Below is the schema of the tables.
Whenever I try to update a value in the parent table I get this error
message:
Error: Cannot delete a parent row: a foreign key constraint fails
I am trying to update a value in the parent, why does the error
message refer to a delete?
-- MySQL dump 8.22
--
-- Host: localhost Database: despathces_inno
---------------------------------------------------------
-- Server version 3.23.53-max-nt
--
-- Table structure for table 'pickup_points'
--
CREATE TABLE pickup_points (
PP_RecordId int(11) NOT NULL auto_increment,
PP_AffId int(11) default NULL,
PP_Code varchar(10) NOT NULL default '',
PP_CompanyName varchar(40) NOT NULL default '',
PP_BranchName varchar(40) NOT NULL default '',
PP_Contact varchar(40) default NULL,
PP_Address1 varchar(40) NOT NULL default '',
PP_Address2 varchar(40) default NULL,
PP_Address3 varchar(40) default NULL,
PP_PostCode varchar(40) default NULL,
PP_Town varchar(40) NOT NULL default '',
PP_County varchar(40) default NULL,
PP_Country varchar(40) NOT NULL default '',
PP_Tel1 varchar(40) default NULL,
PP_Tel2 varchar(40) default NULL,
PP_Fax varchar(40) default NULL,
PP_Email varchar(40) default NULL,
PP_Locality varchar(40) default NULL,
PP_Issuer varchar(40) default NULL,
PP_AffCode varchar(10) default NULL,
PP_Status varchar(10) default 'ACTIVE',
PP_StdCharge varchar(10) default NULL,
PRIMARY KEY (PP_RecordId),
UNIQUE KEY PP_Code (PP_Code),
KEY PP_StdCharge (PP_StdCharge)
) TYPE=InnoDB;
--
-- Table structure for table 'towns_pickup_points'
--
CREATE TABLE towns_pickup_points (
TPP_TownId int(11) NOT NULL default '0',
TPP_PPCode varchar(10) NOT NULL default '',
TPP_Default char(1) default 'Z',
TPP_RecordId int(11) NOT NULL auto_increment,
PRIMARY KEY (TPP_RecordId),
UNIQUE KEY TPP_RecordId (TPP_RecordId),
KEY TPP_TownId (TPP_TownId),
KEY TPP_PPCode (TPP_PPCode),
FOREIGN KEY (`TPP_PPCode`) REFERENCES
`despatches_inno.pickup_points` (`PP_Code`),
) TYPE=InnoDB;
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
