mercredi 31 décembre 2014

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)




Aucun commentaire:

Enregistrer un commentaire