1

I am trying to create a foreign key between the following two tables:

student:

id (PK, Not null, unique, AI)

student_number (Not null, unique)

... (other columns with no constrains)

and

project:

id (pk, not null, unique, AI)

student_number(not null)

... (other columns with no constrains)

The syntax I am using is : 

ALTER table project 
ADD CONSTRAINT fk_project_student FOREIGN KEY (student_number) 
REFERENCES student (student_number) ON DELETE CASCADE ON UPDATE CASCADE;

However I get the following:

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

In case you ask, there are no team projects, so it is a simple one -to - many relationship.

Lazarus Rising
  • 2,597
  • 7
  • 33
  • 58
  • This would suggest that you already have data in the table that is not compatible with the foreign key constraint. – Gordon Linoff Aug 08 '16 at 11:57
  • you can check this answer http://stackoverflow.com/questions/5005388/cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails – Bishoy Bisahi Aug 08 '16 at 12:59

1 Answers1

1

You already have records that do not meet the foreign key constraint. Find them by using

SELECT project.student_id from project 
LEFT JOIN student ON project.student_number = student.student_number
WHERE student.student_number IS NULL
The One and Only ChemistryBlob
  • 7,864
  • 10
  • 36
  • 69
e4c5
  • 52,766
  • 11
  • 101
  • 134
  • Thanks, that was indeed the way to figure out the problem. Some records from table project referenced non-existent values of the table student. The database was not mine to begin with and had a huge amount of rows. It never occurred to me that there would be records from the child table without a parent record because the data were exported from another database with functioning foreign keys. – Lazarus Rising Aug 08 '16 at 13:48
  • Glad to have helped – e4c5 Aug 08 '16 at 14:02