Fraser,
----- Original Message -----
From: "Fraser Hanson" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Wednesday, August 06, 2003 5:46 AM
Subject: ON UPDATE CASCADE fails
> 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.
http://www.innodb.com/ibman.html#InnoDB_foreign_keys
"
A deviation from SQL standards: if ON UPDATE CASCADE or ON UPDATE SET NULL
recurses to update a table for which there already is an update operation in
the stack of cascaded operations, it acts like RESTRICT. In plain English
this means that you cannot use self-referential ON UPDATE CASCADE or ON
UPDATE SET NULL operations. This is to prevent infinite loops resulting from
cascaded updates. A self-referential ON DELETE SET NULL, on the other hand,
works starting from 4.0.13. A self-referential ON DELETE CASCADE has always
worked.
"
InnoDB does not check what rows it has already updated during the SQL
statement. To prevent infinite recursions I let it simply check if it
recurses to the same table.
Since you normally do not update the primary key of a row, ON UPDATE CASCADE
is seldom used in well-designed schemas.
> Any help is greatly appreciated,
> --Fraser Hanson
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
