473,545 Members | 2,135 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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`,`r acetime`),
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
2 36244
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
5889
by: Fraser Hanson | last post by:
Hello, I have a table which has a foreign key relationship with itself. I want and expect my updates to cascade (deletes definitely cascade as expected) but instead I just get error 1217: foriegn key error. I have written example code to use in reproducing the problem: # Create the table
2
39417
by: Gunnar Vøyenli | last post by:
Hi! For the sake of simplicity, I have three tables, Employee, Department and Work Employee >---- Department \ / \ / ^ ^ Work
5
5002
by: Gerald Khin | last post by:
I encountered a performance problem with UPDATE statement and foreign key constraints. There are two tables involved: A parent table A and a child table B: create table A( ID CHAR(15) NOT NULL CONSTRAINT APK PRIMARY KEY, val NUMERIC(10)) create table B(ID CHAR(15) NOT NULL CONSTRAINT BPK PRIMARY KEY,
5
3524
by: PAUL | last post by:
Hello, I have 2 tables with a relationship set up in the dataset with vb ..net. I add a new record to the parent table then edit an existing child record to have the new parent ID. However when I do the update the changed parentid in the child table fails to change. No error is given its just that the change is not written to the Database. ...
13
10102
by: dbuchanan | last post by:
Hello, Here is the error message; ---------------------------- Exception Message: ForeignKeyConstraint Lkp_tbl040Cmpt_lkp302SensorType requires the child key values (5) to exist in the parent table. ----------------------------
3
5427
by: fuimens | last post by:
Hi, With mysql-4.0.20, I have a problem inserting data with foreign key references, MySQL saying ERROR 1216: Cannot add or update a child row: a foreign key constraint fails The message is confusing because a parent record in parent table exists !? The "show innodb status; " command prints :
0
2900
by: Ambica Jain | last post by:
I have a data grid called Files, which has some columns like FileName, Col1, Col2, ... , Col8. Then i have a combobox which allows user to select from Col1 to Col8 and based on this selection, i generate a report from data in grid Files. It displays like (e.g. is Col1 is selected): Col1 Count ---------------------- Val1 ...
13
6549
by: dennis | last post by:
Hello, I'm having trouble solving the following problem with DB2 UDB 8.2. I need to create a trigger that performs certain extra constraint validations (temporal uniqueness). One of the tables has no primary key. I'm having trouble expressing an update trigger that checks for existing rows, contemporary to the updated one, but...
1
5953
by: nitinkhare1 | last post by:
Please help,I'm very frustated. when I ADD the data in kn_history_question table of htbase_tst database....it giving the error "Cannot add or update a child row:a foreign key constraint fails,<'htbase_tst/kn_history_question', CONSTRAINT 'FK_kn_history_question_complaint' FOREIGN KEY ('complaint_id' ) REFERENCES 'complaint'('id')). This...
0
7401
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7656
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7808
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7423
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7757
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5329
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4945
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3443
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
704
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.