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/[EMAIL PROTECTED]
