CODEWITHSUNDEEP
mysql
liverpaul
liverpaul

Reputation: 179

MySQL database, multiple foreign keys

I'm new to the world of MySQL and I'm having a bit of trouble. Here's the problem:

I have 2 tables called TEAMS and FIXTURES. Here's the structure I'm trying to create (only showing relevant fields):

TEAMS table:

FIXTURES table:

home_team and away_team both get a team name from the same source (team_name).

I've successfully created the relationship with the FIXTURES(home_team) and TEAMS(team_name), but I can't get the other relationship working - FIXTURES(away_team) and TEAMS(team_name).

I get the "#1452 - Cannot add or update a child row: a foreign key constraint fails" error message. I'm assuming could be because I have 2 foreign keys in the same table referencing the same primary key. Is that correct? If so, how would I fix it?

Hopefully I've explained this ok and that someone can help me, thanks :-)

Upvotes: 0

Views: 266

Answers (1)

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143279

That means you already have value that doesn't satisfy the constraint, i.e. having no corresponding entry in the teams table. So the problem is not with the schema, but with your data. Fix the issue there.

SELECT * FROM fixtures LEFT JOIN teams ON (away_team=team_name)
WHERE team_name IS NULL;

will fetch you the offending rows.

Upvotes: 1

Related Questions