At 12:10 -0700 9/23/02, j.random.programmer wrote:
>Hi all:
>
>I'm seeing a strange problem updating a field if
>that field is referenced as a FK in another table.
>
>Consider:
>
>CREATE TABLE A (
> id INTEGER NOT NULL AUTO_INCREMENT,
> name VARCHAR(20),
> PRIMARY KEY (id)
>);
>
>CREATE TABLE B (
> id INTEGER NOT NULL AUTO_INCREMENT,
> AID INTEGER,
> name VARCHAR(20),
> PRIMARY KEY (id),
> INDEX(AID) ,
> FOREIGN KEY (AID) REFERENCES A (id)
> ON UPDATE CASCADE
>);
I believe that ON UPDATE CASCADE doesn't work yet.
>
>
>mysql> insert into A values
>(null, 'one'), (null, 'two');
>
>mysql> select * from A;
>+----+------+
>| id | name |
>+----+------+
>| 1 | one |
>| 2 | two |
>+----+------+
>2 rows in set (0.00 sec)
>
>mysql> insert into B values
>(null, 1, 'hello'), (null, 1, 'world');
>
>
>mysql> select * from B;
>+----+------+-------+
>| id | AID | name |
>+----+------+-------+
>| 1 | 1 | hello |
>| 2 | 1 | world |
>+----+------+-------+
>2 rows in set (0.00 sec)
>
>AND HERE IS THE PROBLEM:
>
>mysql> update A set id = 5 where id = 1;
>ERROR 1217: Cannot delete a parent row: a foreign key
>constraint fails
>
>---- Note, the innodb manual says: ----
>If you defined ON DELETE CASCADE or SET NULL and
>updated the referenced key in the parent row, InnoDB
>deleted or updated the child row. This is now changed
>to conform to SQL-92: you get the error 'Cannot delete
>parent row'.
>---------------------------------------------------
>
>NOTE, There is no 'ON DELETE' constraint in my example
>so what on earth is going on ?
>
>Can anyone explain why an update is not getting
>cascaded ? I mean, that's the whole POINT of the
>update constraint. What am I doing wrong ?
>
>Best regards,
>
>--j
