Foreign key set to cascading but fails to update - why?

As mentioned elsewhere, I am rather new to SQL and databases. Getting my hands dirty at the moment and trying all the different possibilities.

I have a server running MySQL and set up the following table for testing:

Field Type Collation Attributes Null Default Extra
id mediumint(8) UNSIGNED No None AUTO_INCREMENT
domain varchar(255) utf8_unicode_ci No None
status enum('...') utf8_unicode_ci No None
replaced_by mediumint(8) UNSIGNED Yes NULL

The idea was to have a list of domains that are valid for emails such as "gmail.com" and "hotmail.com".
Now if some domain is recognized as duplicate to another (such as "googlemail.com" to "gmail.com") the status column indicates this and the "replaced_by" column gives the ID to the main domain to be used.

Also, if a domain is recognized as invalid (such as "gymail.com" instead of "gmail.com") the database could store this information and, through status and replaced_by a script could suggest the correct domain.

While ID is the primary key of this table, I wanted to test a foreign key on the replaced_by column with ON DELETE RESTRICT and ON UPDATE CASCASE. so that the replaced_by column stays in-sync with the main ID.

But somehow when I try to update data, I get the following error. Why?

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`domains`, CONSTRAINT `domains_ibfk_1` FOREIGN KEY (`replaced_by`) REFERENCES `domains` (`id`) ON UPDATE CASCADE)

EDIT - some more details to help resolve this case:

``
CREATE TABLE test.domains (
id MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
domain VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
status ENUM( 'valid', 'invalid', 'replace' ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
replaced_by MEDIUMINT( 8 ) UNSIGNED NULL ,
INDEX ( re

It appears as if this is not permitted in innodb which I assume is the engine you are using. From http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html:


If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. 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, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.

StackExchange Database Administrators Q#87369, answer score: 5