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.
