MySQL Bugs: #13690: ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint

Bug #13690	ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint
Submitted:	2 Oct 2005 16:28	Modified:	17 Nov 2005 9:50
Reporter:	Thiam Joo Tay	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.12a-nt	OS:	Windows (WinXP Pro)
Assigned to:		CPU Architecture:	Any

Description:
Hi All
I have tried to populate the table but it seems that the error keep prompting,
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails.

However, when I use the Insert statement to insert the data, it manage to insert successfully. Why is this so ? Is there a bug ???

Here are my tables. it rather simple though,

mysql> CREATE TABLE Student (
        -> sid INTEGER,
        -> sname VARCHAR(25),
        -> programme VARCHAR(50),
        -> level INTEGER,
        -> age INTEGER,
        -> CONSTRAINT Student_sid_pk PRIMARY KEY(sid));

mysql> CREATE TABLE Department (
        -> depid INTEGER,
        -> depname VARCHAR(25),
        -> CONSTRAINT Department_depid_pk PRIMARY KEY(depid));

mysql> CREATE TABLE Instructor (
        -> insid INTEGER,
        -> insname VARCHAR(25),
        -> depid INTEGER,
        -> CONSTRAINT Instructor_insid_pk PRIMARY KEY(insid),
        -> CONSTRAINT Instructor_depid_fk FOREIGN KEY(depid) REFERENCES
        -> Department(depid));

mysql> CREATE TABLE Class (
        -> cname VARCHAR(25),
        -> meets_at time,
        -> week_day VARCHAR(10),
        -> room VARCHAR(12),
        -> insid INTEGER,
        -> CONSTRAINT Class_cname_pk PRIMARY KEY(cname),
        -> CONSTRAINT Class_insid_fk FOREIGN KEY(insid) REFERENCES Instructor(insid));

mysql> CREATE TABLE Enrolled (
        -> sid INTEGER,
        -> cname VARCHAR(25),
        -> CONSTRAINT Enrolled_sid_cname_pk PRIMARY KEY(sid,cname),
        -> CONSTRAINT Enrolled_sid_fk FOREIGN KEY(sid) REFERENCES Student(sid),
        -> CONSTRAINT Enrolled_cname_fk FOREIGN KEY(cname) REFERENCES Class(cname));

How to repeat:
Try to populate each tables, the last table to populate should be the Enrolled table. The error will appear as,
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails.

Thank you for a problem report.

Please, send the exact sequence of INSERT statements you used before getting this error message. We need a repeatable test case to analyze is it a bug or not.

The error occurs when I used the Load data infile statement

LOAD DATA LOCAL INFILE '/Enrolled.txt' INTO TABLE Enrolled;

We need a content of the file you are trying to load. Please, upload it using the File tab. You may chose to upload it as private, visible only to MySQL developers. Inform about the results or problems with this, if any.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".