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:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.12a-nt OS:Microsoft Windows (WinXP Pro)
Assigned to: CPU Architecture:Any

[2 Oct 2005 16:28] Thiam Joo Tay
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.
[3 Oct 2005 7:11] Valeriy Kravchuk
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.
[16 Oct 2005 3:55] Thiam Joo Tay
The error occurs when I used the Load data infile statement

[17 Oct 2005 9:50] Valeriy Kravchuk
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.
[18 Nov 2005 0:00] Bugs System
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".