ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
Distribution: Fedora 3,4- Ubuntu 6.06 to 8.10, Gentoo and Arch
Posts: 408
Rep:
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.
Distribution: Fedora 3,4- Ubuntu 6.06 to 8.10, Gentoo and Arch
Posts: 408
Original Poster
Rep:
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.
Distribution: Fedora 3,4- Ubuntu 6.06 to 8.10, Gentoo and Arch
Posts: 408
Original Poster
Rep:
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.