LinuxQuestions.org - Mysql foreign key doesn't allow update

Hi,

I have two mysql tables whose structures are defined using the following sql statements:

PHP Code:




CREATE TABLE `matchResult` ( 
  `match_id` int(16) NOT NULL auto_increment, 
  `league_id` int(8) NOT NULL, 
  `weekNumber` int(8) NOT NULL, 
  `team1_id` int(3) NOT NULL, 
  `team2_id` int(3) NOT NULL, 
  `result1` int(2) default NULL, 
  `result2` int(2) default NULL, 
  `date` datetime NOT NULL, 
  `status` tinyint(4) NOT NULL, 
  PRIMARY KEY  (`match_id`), 
  KEY `team1_id` (`team1_id`), 
  KEY `team2_id` (`team2_id`), 
  CONSTRAINT `matchResult_ibfk_1` FOREIGN KEY (`team1_id`) 
 REFERENCES `teams` (`team_id`) ON DELETE CASCADE ON UPDATE CASCADE, 
  CONSTRAINT `matchResult_ibfk_2` FOREIGN KEY (`team2_id`) 
 REFERENCES `teams` (`team_id`) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

PHP Code:




CREATE TABLE `predicts` ( 
  `predict_id` int(8) NOT NULL auto_increment, 
  `match_id` int(16) NOT NULL, 
  `user_id` int(16) NOT NULL, 
  `result1` int(2) NOT NULL, 
  `result2` int(2) NOT NULL, 
  `date` datetime NOT NULL, 
  `isPredicted` tinyint(4) NOT NULL, 
  `lockDate` datetime default NULL, 
  `isLocked` tinyint(4) NOT NULL, 
  PRIMARY KEY  (`predict_id`), 
  KEY `user_id` (`user_id`), 
  KEY `match_id` (`match_id`), 
  CONSTRAINT `predicts_ibfk_2` FOREIGN KEY (`match_id`) 
 REFERENCES `matchResult` (`match_id`) ON DELETE CASCADE ON UPDATE CASCADE, 
  CONSTRAINT `predicts_ibfk_1` FOREIGN KEY (`user_id`) 
 REFERENCES `users` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

My PHP program can insert into the predicts table, even though it returns "Cannot add or update a child row: a foreign key constraint fails" error.

However, when I want to update the same table, it gives me the same error with no changes on the table.

I tried different combinations of on delte and on update actions, but I cannot get rid of this error. I also made sure that referred all referred columns have exact same type and length as foreign keys.

Any idea what's going on here?

Thanks in advance.