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 ); 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 __________________________________________________ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com --------------------------------------------------------------------- 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
