CODEWITHSUNDEEP
mysqldjangodjango-modelsdjango-southdatabase-migration
mynk
mynk

Reputation: 1234

MySQL fails to add a foreign key constraint

I added a table 'GameRatings' in the Django model and migrated using South. The table has a one-one relationship with a table called 'Game'.

The migration fails with the following error message:

2014-01-07 10:51:15,026] (0.505) CREATE TABLE app_gameratingsmodel (game_id varchar(32) NOT NULL PRIMARY KEY, averageRating double precision NOT NULL, numRatings integer NOT NULL); args=[]

[2014-01-07 10:51:15,650] (0.294) ALTER TABLE app_gameratingsmodel ADD CONSTRAINT game_id_refs_gameID_fe5d3728 FOREIGN KEY (game_id) REFERENCES app_gamemodel (gameID);; args=[]

FATAL ERROR - The following SQL query failed: ALTER TABLE app_gameratingsmodel ADD CONSTRAINT game_id_refs_gameID_fe5d3728 FOREIGN KEY (game_id) REFERENCES app_gamemodel (gameID);

The error was: (1822, "Failed to add the foreign key constaint. Missing index for constraint 'game_id_refs_gameID_fe5d3728' in the referenced table 'app_gamemodel'")

I don't understand what index is MySQL talking about. Can anyone help?

Thanks.

Upvotes: 2

Views: 6178

Answers (3)

Brian
Brian

Reputation: 61

This occures often when you try reference two columns with different data type (e.g. VARCHAR to INT or even INT to UINT!). I always forget to check the unsigned marker in Workbench :)

Upvotes: 3

mynk
mynk

Reputation: 1234

Okay, I figured. It turns out that there was a charset mismatch between the columns for both tables.

The charset was utf8 for the GameTable where as the new one was being created with charset latin since that was the DB's default charset. :)

Upvotes: 1

mjl
mjl

Reputation: 197

You probably have no UNIQUE constraint on app_gamemodel.gameID. A UNIQUE (or PRIMARY KEY, which is pretty much the same) is needed for a database to be able to establish a foreign key constraint.

Upvotes: 2

Related Questions