LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 07-25-2008, 07:35 PM   #1
mohtasham1983
Member
 
Registered: Apr 2005
Location: San Jose
Distribution: Fedora 3,4- Ubuntu 6.06 to 8.10, Gentoo and Arch
Posts: 408

Rep: Reputation: 30
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.
 
Old 07-25-2008, 08:42 PM   #2
mohtasham1983
Member
 
Registered: Apr 2005
Location: San Jose
Distribution: Fedora 3,4- Ubuntu 6.06 to 8.10, Gentoo and Arch
Posts: 408

Original Poster
Rep: Reputation: 30
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.
 
Old 07-25-2008, 09:53 PM   #3
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
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
 
Old 07-25-2008, 10:56 PM   #4
mohtasham1983
Member
 
Registered: Apr 2005
Location: San Jose
Distribution: Fedora 3,4- Ubuntu 6.06 to 8.10, Gentoo and Arch
Posts: 408

Original Poster
Rep: Reputation: 30
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.
 
  


Reply

Tags
key, mysql, update


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Allow foreign clients to connect to mysql server howto bipinkdas Linux - Server 1 05-14-2008 08:05 PM
Processing data from a 'foreign' database with mysql, or tools to pre-process data. linker3000 Linux - Software 1 08-14-2007 08:36 PM
can see gpg key in apt-key, still can't update Dan63043 Ubuntu 2 09-25-2006 11:35 AM
Bad Key / No Key Error MySql wasabi Linux - Software 1 04-19-2005 06:06 PM
Mysql Foreign Key munna_502 Linux - Software 0 06-12-2004 04:31 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 07:45 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration