Morten, http://www.innodb.com/ibman.php#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. "
I realize InnoDB should give an error if you try to create a cyclic ON UPDATE CASCADE constraint. Or even better, we should implement it so that also that would work! Though the behavior in the case where the constraint recurses to update the same row twice is problematic. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ .............................................. List:MySQL General Discussion« Previous MessageNext Message » From:Morten GulbrandsenDate:January 9 2004 1:47pm Subject:ON UPDATE CASCADE Hello, If I change the value of a reference a , for instance by means of updating or inserting values, I'd expect both updated values and inserted values to cascade, hence to change b, where FOREIGN KEY (b) REFERENCES A(a) ON UPDATE CASCADE In this example foreign key and reference are taken from one and the same table, that should be possible, please help me , what is wrong ? My code woun't cascade anything. Yours Sincerely Morten Gulbrandsen =================== -------------- DROP TABLE IF EXISTS A -------------- Query OK, 0 rows affected (0.04 sec) -------------- CREATE TABLE A ( a CHAR(9) NOT NULL, b CHAR(9), c INT NOT NULL DEFAULT 1, PRIMARY KEY (a), INDEX (b, c) )ENGINE = INNODB -------------- Query OK, 0 rows affected (0.05 sec) -------------- SHOW WARNINGS -------------- Empty set (0.00 sec) -------------- DESCRIBE A -------------- +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | char(9) | | PRI | | | | b | char(9) | YES | MUL | NULL | | | c | int(11) | | | 1 | | +-------+---------+------+-----+---------+-------+ 3 rows in set (0.01 sec) -------------- ALTER TABLE A ADD FOREIGN KEY (b) REFERENCES A(a) ON DELETE SET NULL ON UPDATE CASCADE -------------- Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
