LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   Mysql foreign key doesn't allow update (http://www.linuxquestions.org/questions/programming-9/mysql-foreign-key-doesnt-allow-update-658304/)

mohtasham1983 07-25-2008 07:35 PM

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_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`)
 
REFERENCES `teams` (`team_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  
CONSTRAINT `matchResult_ibfk_2FOREIGN 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_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`)
 
REFERENCES `matchResult` (`match_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  
CONSTRAINT `predicts_ibfk_1FOREIGN 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.

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.