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)
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")insert into domain (id, domain, status, replaced_by) values (1, '', 'valid', 1);