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

Question

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 ( `replaced_by` ) ,
UNIQUE (
`domain`
)

ALTER TABLE `domains` ADD FOREIGN KEY ( `replaced_by` ) REFERENCES `test`.`domains` (
`id`
) ON DELETE RESTRICT ON UPDATE CASCADE ;

UPDATE `test`.`domains` SET `id` = '31' WHERE `domains`.`id` =1

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

Answer 1

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.

Answer 2

This issue would occur if two columns in the same child table with the foreign key referring to the same parent table column "id". Please do check the same and modifies the child column to fix this issue.