-   Programming (
-   -   Mysql foreign key doesn't allow update (

mohtasham1983 07-25-2008 07:35 PM

Mysql foreign key doesn't allow update

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

PHP Code:

CREATE TABLE `matchResult` (
match_idint(16NOT NULL auto_increment,
league_idint(8NOT NULL,
weekNumberint(8NOT NULL,
team1_idint(3NOT NULL,
team2_idint(3NOT NULL,
result1int(2) default NULL,
result2int(2) default NULL,
datedatetime NOT NULL,
statustinyint(4NOT NULL,
PRIMARY KEY  (`match_id`),
KEY `team1_id` (`team1_id`),
KEY `team2_id` (`team2_id`),
CONSTRAINT `matchResult_ibfk_1FOREIGN KEY (`team1_id`)
CONSTRAINT `matchResult_ibfk_2FOREIGN KEY (`team2_id`)

PHP Code:

CREATE TABLE `predicts` (
predict_idint(8NOT NULL auto_increment,
match_idint(16NOT NULL,
user_idint(16NOT NULL,
result1int(2NOT NULL,
result2int(2NOT NULL,
datedatetime NOT NULL,
isPredictedtinyint(4NOT NULL,
lockDatedatetime default NULL,
isLockedtinyint(4NOT NULL,
PRIMARY KEY  (`predict_id`),
KEY `user_id` (`user_id`),
KEY `match_id` (`match_id`),
CONSTRAINT `predicts_ibfk_2FOREIGN KEY (`match_id`)
CONSTRAINT `predicts_ibfk_1FOREIGN KEY (`user_id`)

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.

mohtasham1983 07-25-2008 08:42 PM

I just tried to exact same query from mysql terminal, and i was able to do it. Then I tried to update the value of match_id to a value that is not available in matchResult, and it didn't allow me to execute the query correctly.

I think my problem is with my php application.

graemef 07-25-2008 09:53 PM

get php to print out the exact query that is being executed. You may find that a variable is not set up as it should be

mohtasham1983 07-25-2008 10:56 PM

Thank you. I found out what the problem was. This query was executing inside the loop. The loop was supposed to continue n/2 times, but in my loop I had written n times. That's why, after (n/2)+1 times, it was trying to insert null values in the tables.

All times are GMT -5. The time now is 02:29 PM.