Manixman Manixman - 6 months ago 15
SQL Question

#1452 error in sql means I can't add foreign key

ALTER TABLE Question ADD CONSTRAINT FK_SessionQuestion FOREIGN KEY
(SessionId) REFERENCES Session (SessionId);


SQL above gives me error below:


#1452 - Cannot add or update a child row: a foreign key constraint fails (
mobile_app
.
#sql-567_20be3
, CONSTRAINT
FK_SessionQuestion


FOREIGN KEY (
SessionId
) REFERENCES
Session
(
SessionId
))



I want to include a foreign key in
Question
table to link up with
SessionId
but why won't it let me and how can it be fixed?

Question
table:

SessionId(PK) QuestionId(PK) QuestionContent
--------------------------------------------
1 1 What is 2+2
1 2 What is 3+3
2 1 What is 5+5
2 2 What is 4+7


Session
table:

SessionId SessionName
---------------------
1 AAA

Answer

The question table includes SessionIds that are not found in the session table (well, actually, just one: 2). The FOREIGN KEY constraint, however, requires every SessionId used in the question table to exist in the session table by its definition.

Either insert all missing sessions, or remove all questions that reference sessions that don't exist.


To get a list of all SessionIds you're missing in the table Session, you can use a LEFT JOIN:

SELECT DISTINCT Question.SessionId
FROM Question
     LEFT JOIN Session
       ON Question.SessionId = Session.SessionId
WHERE Session.SessionId IS NULL;