Freya Freya - 4 months ago 8
MySQL Question

Foreign key constraint not being implemented

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.

Answer

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

SELECT project.student_id from project 
LEFT JOIN student ON student_number 
WHERE student.student_number IS NULL