By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,830 Members | 1,758 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,830 IT Pros & Developers. It's quick & easy.

Cannot add or update a child row: a foreign key constraint fails

P: n/a
I'm having trouble copying a database to another machine. Here are the two
table's in ths database and the sql commands:
DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
`client_id` int(10) unsigned NOT NULL auto_increment,
`lastname` char(15) NOT NULL default '',
`firstname` char(15) NOT NULL default '',
`birthdate` date NOT NULL default '0000-00-00',
`middlename` char(15) NOT NULL default '',
`username` char(20) NOT NULL default '',
`kgs` float(5,2) NOT NULL default '0.00',
`cm` float(5,2) NOT NULL default '0.00',
`sex` char(1) NOT NULL default '',
`phone` char(16) NOT NULL default '',
`street` char(30) NOT NULL default '',
`city` char(20) NOT NULL default '',
`state` char(10) NOT NULL default '',
`country` char(3) NOT NULL default '',
`zip` char(12) NOT NULL default '',
`lower_hr` int(10) unsigned NOT NULL default '0',
`upper_hr` int(10) unsigned NOT NULL default '0',
`econtact` char(20) NOT NULL default '',
`ephone` char(16) NOT NULL default '',
`athleteType` char(16) NOT NULL default '',
`password` char(20) NOT NULL default '',
`email` char(40) NOT NULL default '',
`logins` int(11) NOT NULL default '0',
`comment` char(128) NOT NULL default '',
`at` float(6,2) default NULL,
PRIMARY KEY (`client_id`),
KEY `lastname` (`lastname`),
KEY `firstname` (`firstname`),
KEY `birthdate` (`birthdate`),
KEY `username` (`username`),
KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into clients... This works fine. I have the correct number of entries.

DROP TABLE IF EXISTS `perfs`;
CREATE TABLE `perfs` (
`client_id` int(10) unsigned NOT NULL default '0',
`racetime` datetime NOT NULL default '0000-00-00 00:00:00',
`coursename` char(80) NOT NULL default '',
`rank` int(10) unsigned NOT NULL default '0',
`ms` int(10) unsigned NOT NULL default '0',
`location` char(20) NOT NULL default '',
`software` char(20) NOT NULL default '',
`rr` float NOT NULL default '0',
`avgmph` float NOT NULL default '0',
`peakmph` float NOT NULL default '0',
`avgwatts` float NOT NULL default '0',
`peakwatts` float NOT NULL default '0',
`avghr` float NOT NULL default '0',
`peakhr` float NOT NULL default '0',
`avgrpm` float NOT NULL default '0',
`peakrpm` float NOT NULL default '0',
`avgpp` float NOT NULL default '0',
`peakpp` float NOT NULL default '0',
`avgss` float NOT NULL default '0',
`calories` float NOT NULL default '0',
`event_type` char(20) NOT NULL default '',
`perf_file` char(64) NOT NULL default '',
PRIMARY KEY (`client_id`,`racetime`),
KEY `coursename` (`coursename`),
KEY `ms` (`ms`),
KEY `client_id` (`client_id`),
KEY `perf_file` (`perf_file`),
KEY `rank` (`rank`),
CONSTRAINT `perfs_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into perfs.... I get the following error:

ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails;

At this point clients has records, but perfs has no records in it.
I think that this error means that a perfs record has a client_id that doesn't exist
in the clients table. But how do I find out which record innodb is complaining about?
I have a few hundred records in both clients and perfs. What does the 23000 refer to?

Finally, how did my database get messed up? I'm trying to restore from a mysqldump backup from
another machine and I expected the database to not have an error like this. Is there
a way to make sure that this doesn't happen again?

thanks,
Ralph

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
If you already have the tables somewhere (perhaps in a temporary,
non-relational setup), you can use a left join to find "orphaned" rows.
Here's my orphan query, written for PHP. This will find records in $t1
that refer to non-existant rows in $t2. Just fill in the variables.

SELECT t1.$key
FROM $t1 AS t1
LEFT JOIN $t2 AS t2
ON t1.$foreign_key = t2.$key
WHERE t2.$key IS NULL

I can't speak to how the data got messed up in the first place, since
I've always handled relationships through my code.

Jul 23 '05 #2

P: n/a
Ralph Smith wrote:
insert into perfs.... I get the following error:

ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails;


You could be having a chicken-and-egg problem with regards to foreign
key references. The restore is trying to insert records in the
referencing table before it has restored the values in the referenced table.

I remember a recent thread on this newsgroup, in March 2005, on a
similar problem. The subject was "mysqldump and restore of Innodb
Tables". Try reading this thread and see if it helps:

http://groups-beta.google.com/group/...77440ae38edfc4

Regards,
Bill K.
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.