[MySQL] Error in ALTER TABLE when adding a foreign key constraint

[Mayank Bhagya]

I have been using Django & South with MySQL for quite some time now.

I have a table called 'Game' with primary key 'gameID'.

I am trying to create another table called 'GameRating' which has a one-one relation with the Game table.

Hence I added the following code in 'models.py':

class GameRatingModel(models.Model): 
    game = models.OneToOneField(GameModel, primary_key=True)
    averageRating = models.FloatField()
    numRatings = models.IntegerField()

 

The migration fails with the following error:

[2014-01-07 06:58:26,545] (0.427) CREATE TABLE `app_ratingmodel` (`game_id` varchar(32) NOT NULL PRIMARY KEY, `averageRating` double precision NOT NULL, `numRatings` integer NOT NULL); args=[]
[2014-01-07 06:58:27,147] (0.314) ALTER TABLE `app_ratingmodel` 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_ratingmodel` 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 went wrong. Can anyone please help?

Thanks.

-mayank

[Shai Berger]

At first glance, it looks like MySQL is complaining that there is no index on
the gameID column -- which would indicate that it is not really a primary key,
at least at the database level.

Can you verify the app_gamemodel table definition?

On Tuesday 07 January 2014 12:53:28 Mayank Bhagya wrote:
> I have been using Django & South with MySQL for quite some time now.
>
> I have a table called 'Game' with primary key 'gameID'.
> I am trying to create another table called 'GameRating' which has a one-one
> relation with the Game table.
>
> Hence I added the following code in 'models.py':

> > *class GameRatingModel(models.Model): *

[Mayank Bhagya]

Thanks Shai.

I think I figured what the problem is. I dug into the CREATE statements of the two tables and realised that both of them have different character sets!

I don't understand why the new table is being created with 'latin' character set and the old one is with 'utf8'.

So the datatype for both the fields does not match even though both are varchar(32).

[Mayank Bhagya]

Okay, the default charset for my DB was latin. :)