CODEWITHSUNDEEP
mysqlforeign-keys
Lazarus Rising
Lazarus Rising

Reputation: 2685

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.

Upvotes: 1

Views: 318

Answers (1)

e4c5
e4c5

Reputation: 53774

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

Upvotes: 1

Related Questions