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

ON UPDATE CASCADE fails

P: n/a
Hello,

I have a table which has a foreign key relationship with itself. I
want and expect my updates to cascade (deletes definitely cascade as
expected) but instead I just get error 1217: foriegn key error.

I have written example code to use in reproducing the problem:
# Create the table
drop TABLE IF EXISTS person;
create table person (
name VARCHAR(50) NOT NULL,
parent VARCHAR(50) NOT NULL,
INDEX parent_ind(parent),
FOREIGN KEY(parent)
REFERENCES person(name) # this makes it so that when a page is deleted/updated, so are all it's sub-pages
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (name)
) TYPE=INNODB;

# Insert some example data
INSERT INTO person (name,parent) VALUES ('Grandpa', 'Grandpa');
INSERT INTO person (name,parent) VALUES ('John', 'Grandpa');
INSERT INTO person (name,parent) VALUES ('Baby Bob', 'John');

mysql> select * from person;
+----------+---------+
| name | parent |
+----------+---------+
| Grandpa | Grandpa |
| John | Grandpa |
| Baby Bob | John |
+----------+---------+

# Attempt the update (this fails with error 1217)
UPDATE person SET name='Mary' where name='John';
# I expect to end up with these values:
mysql> select * from person;
+----------+---------+
| name | parent |
+----------+---------+
| Grandpa | Grandpa |
| Mary | Grandpa |
| Baby Bob | Mary |
+----------+---------+

I have verified that deletes cascade as expected.
After attempting the update query, SHOW INNODB STATUS reports the following:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030805 19:28:57 Transaction:
TRANSACTION 0 13261, ACTIVE 0 sec, OS thread id 664 updating or deleting, thread declared inside InnoDB 499
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 307, query id 2804 localhost 127.0.0.1 root Updating
UPDATE person SET name='Mary' where name='John'
Foreign key constraint fails for table test/person: ,
CONSTRAINT `0_568` FOREIGN KEY (`parent`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
Trying an update, possibly causing a cyclic cascaded update
in the child table, in parent table, in index PRIMARY tuple:
0: len 4; hex 4a6f686e; asc John;; 1: len 6; hex 0000000033cd; asc ....3.;; 2:
len 7; hex 000000003700f4; asc ....7.;; 3: len 7; hex 4772616e647061; asc Grandpa;;
But in child table test/person, in index parent_ind, there is a record:
RECORD: info bits 0 0: len 4; hex 4a6f686e; asc John;; 1: len 8; hex 4261627920426f62; asc Baby Bob;;
------------

I have verified that the problem is not related to the self-referential row at
the start by turning off foreign key checks, deleting that row, turning
foreign_key_checks back on and then attempting the update again. This still
produces error 1217.

Is this an official mysql bug, or am I missing something?

Any help is greatly appreciated,
--Fraser Hanson

--
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.