Legend Chen Legend Chen - 1 month ago 12
MySQL Question

MySQL error when update foreign key

I have 2 tables following below:

CREATE TABLE `queststatus` (
`queststatusid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`characterid` int(11) NOT NULL DEFAULT '0',
`quest` int(6) NOT NULL DEFAULT '0',
`status` tinyint(4) NOT NULL DEFAULT '0',
`time` int(11) NOT NULL DEFAULT '0',
`forfeited` int(11) NOT NULL DEFAULT '0',
`customData` varchar(255) DEFAULT NULL,
PRIMARY KEY (`queststatusid`),
KEY `characterid` (`characterid`),
CONSTRAINT `queststatus_ibfk_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2148654268 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED


and

CREATE TABLE `queststatusmobs` (
`queststatusmobid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`queststatusid` int(10) unsigned NOT NULL DEFAULT '0',
`mob` int(11) NOT NULL DEFAULT '0',
`count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`queststatusmobid`),
KEY `queststatusid` (`queststatusid`),
CONSTRAINT `queststatusmobs_ibfk_1` FOREIGN KEY (`queststatusid`) REFERENCES `queststatus` (`queststatusid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


So,when I update the queststatusid of table queststatus, I use these command because the queststatusid reached the maximum of INT:

SET foreign_key_checks = 1;
SET @newid=0;
UPDATE queststatus SET queststatusid=(@newid:=@newid+1) ORDER BY queststatusid;


this command should makes queststatusid of table queststatusmobs change when queststatusid of of table queststatus update, but it doesn't.
It shows:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`twms`.`queststatus`, CONSTRAINT `queststatus_ibfk_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)


I have deleted all useless foreign key which not in queststatus by using this command first:

DELETE FROM queststatusmobs WHERE queststatusid not in (SELECT queststatusid FROM queststatus);


How can I solve this problem?

Answer

You didn't have that particular id is not available on your characters table.

First you insert characters table id and used that id as a foreign key on queststatus table.

Or

Remove the foreign key reference from queststatus.