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