5

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)
  • What did you try to update? Update an "id" to be a different (unused) value? – Michael - sqlbot Jan 1 '15 at 3:42
  • @Michael-sqlbot yes I tried that. I also tried to update replaced_by just to see if that would work, but no. No matter which one I try to update, I get this error. (one of them saying "cannot update parent row" and the other saying "cannot update child row") – koljanep Jan 1 '15 at 9:45
  • Can you post some data (insert statements) that illustrates the problem? – Lennart Jan 1 '15 at 19:42
  • No need to, one row is enough to demonstrate the problem: insert into domain (id, domain, status, replaced_by) values (1, '', 'valid', 1); – Lennart Jan 1 '15 at 19:50
5

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.

  • Ok this answers my question, thanks. But what would be the correct handling for this? Is there another way to set things up the way I want, or do I have to handle it with a script? – koljanep Jan 2 '15 at 8:07
  • 1
    Is this a one time operation or something that happens frequently? – Lennart Jan 2 '15 at 8:08
  • The above is right now just a test example but I already figured out 2 ways how I would implement this into my sites. At that point it would be something that would occur regularly on my first, and very rarely on my second implementation. – koljanep Jan 2 '15 at 8:14
  • For those rare moments, make sure no one can modify the table, drop the f.k, do the update on both columns, recreate the f.k. – Lennart Jan 2 '15 at 8:17
  • 1
    Create a new table for replaced_by. – Lennart Jan 2 '15 at 8:41
0

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.

  • I have only one foreign key set up. The foreign key refers to the primary key in the same table. Is this a problem? – koljanep Jan 1 '15 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.