Description:
When attempting to change a value in a master table a foreign key record with two separate fields referencing the same field being changed fails. I am using the InnoDB engine.
How to repeat:
CREATE TABLE `devMembers` ( `Member` varchar(7) NOT NULL default '', `Mode` enum('Normal','Special','Expired','Deleted') default 'Normal', `FirstName` varchar(20) NOT NULL default '', `LastName` varchar(30) NOT NULL default '', `Address` varchar(40) NOT NULL default '', `Zip` varchar(5) NOT NULL default '85374', `Phone` varchar(20) default NULL, `Email` varchar(40) default NULL, `Joined` date NOT NULL default '0000-00-00', `Expires` date NOT NULL default '0000-00-00', `Password` varchar(40) NOT NULL default '', `Hint` varchar(40) default NULL, `UserType` enum('Full','Turner','Carver') default NULL, `SerialNum` int(11) NOT NULL default '0', `Notes` text, `Spouse` varchar(20) default NULL, PRIMARY KEY (`Member`), KEY `LastName` (`LastName`), KEY `Phone` (`Phone`), KEY `Email` (`Email`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `devVolServe` ( `Member` varchar(7) NOT NULL default '', `Served` datetime NOT NULL default '0000-00-00 00:00:00', `Position` int(11) NOT NULL default '0', `Entered` datetime default NULL, `EnterBy` varchar(7) default NULL, `Credit` int(11) default NULL, PRIMARY KEY (`Member`,`Served`,`Position`), KEY `Member` (`Member`), KEY `EnterBy` (`EnterBy`), KEY `Position` (`Position`), CONSTRAINT `devVolServe_ibfk_1` FOREIGN KEY (`Member`) REFERENCES `devMembers` (`Member`) ON UPDATE CASCADE, CONSTRAINT `devVolServe_ibfk_2` FOREIGN KEY (`EnterBy`) REFERENCES `devMembers` (`Member`) ON UPDATE CASCADE, CONSTRAINT `devVolServe_ibfk_3` FOREIGN KEY (`Position`) REFERENCES `devVolPos` (`Position`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
select * from devMembers where Member = '5111-001'
Member Served PositionEntered EnterBy Credit
5111001 0000-00-00 00:00:00 14 4
5111001 2016-01-16 00:00:00 14 5111001 4
update devMembers set Member = '5111000' where Member = '5111001'
Query failed. MySQL error: (1452) Cannot add or update a child row: a foreign key constraint fails (`woodcrafters/devVolServe`, CONSTRAINT `devVolServe_ibfk_2` FOREIGN KEY (`EnterBy`) REFERENCES `devMembers` (`Member`) ON UPDATE CASCADE)
Description: When attempting to change a value in a master table a foreign key record with two separate fields referencing the same field being changed fails. I am using the InnoDB engine. How to repeat: CREATE TABLE `devMembers` ( `Member` varchar(7) NOT NULL default '', `Mode` enum('Normal','Special','Expired','Deleted') default 'Normal', `FirstName` varchar(20) NOT NULL default '', `LastName` varchar(30) NOT NULL default '', `Address` varchar(40) NOT NULL default '', `Zip` varchar(5) NOT NULL default '85374', `Phone` varchar(20) default NULL, `Email` varchar(40) default NULL, `Joined` date NOT NULL default '0000-00-00', `Expires` date NOT NULL default '0000-00-00', `Password` varchar(40) NOT NULL default '', `Hint` varchar(40) default NULL, `UserType` enum('Full','Turner','Carver') default NULL, `SerialNum` int(11) NOT NULL default '0', `Notes` text, `Spouse` varchar(20) default NULL, PRIMARY KEY (`Member`), KEY `LastName` (`LastName`), KEY `Phone` (`Phone`), KEY `Email` (`Email`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `devVolServe` ( `Member` varchar(7) NOT NULL default '', `Served` datetime NOT NULL default '0000-00-00 00:00:00', `Position` int(11) NOT NULL default '0', `Entered` datetime default NULL, `EnterBy` varchar(7) default NULL, `Credit` int(11) default NULL, PRIMARY KEY (`Member`,`Served`,`Position`), KEY `Member` (`Member`), KEY `EnterBy` (`EnterBy`), KEY `Position` (`Position`), CONSTRAINT `devVolServe_ibfk_1` FOREIGN KEY (`Member`) REFERENCES `devMembers` (`Member`) ON UPDATE CASCADE, CONSTRAINT `devVolServe_ibfk_2` FOREIGN KEY (`EnterBy`) REFERENCES `devMembers` (`Member`) ON UPDATE CASCADE, CONSTRAINT `devVolServe_ibfk_3` FOREIGN KEY (`Position`) REFERENCES `devVolPos` (`Position`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 select * from devMembers where Member = '5111-001' Member Served PositionEntered EnterBy Credit 5111001 0000-00-00 00:00:00 14 4 5111001 2016-01-16 00:00:00 14 5111001 4 update devMembers set Member = '5111000' where Member = '5111001' Query failed. MySQL error: (1452) Cannot add or update a child row: a foreign key constraint fails (`woodcrafters/devVolServe`, CONSTRAINT `devVolServe_ibfk_2` FOREIGN KEY (`EnterBy`) REFERENCES `devMembers` (`Member`) ON UPDATE CASCADE)