How can I have a unique_together constraint involving a ForeignKey field in MySQL?

Question

MySQL seems to be sort of broken with regards to multi-column unique constraints with foreign keys. Here's the smallest example I can come up with to show this (using MySQL/InnoDB):

models.py

from django.db import models

class Team(models.Model):
    pass

class Player(models.Model):
    team = models.ForeignKey(Team)
    number = models.PositiveIntegerField()

    class Meta:
        unique_together = ("team", "number")

Running schemamigration --initial, south spits out the following migration (important bits only):

class Migration(SchemaMigration):                                                                                                                                                
    def forwards(self, orm):
        # Adding model 'Team'
        db.create_table('fkuniq_team', (
            ('id', self.gf('django.db.models.fields.AutoField')(primary_key=True)),
        ))
        db.send_create_signal('fkuniq', ['Team'])

        # Adding model 'Player'
        db.create_table('fkuniq_player', (
            ('id', self.gf('django.db.models.fields.AutoField')(primary_key=True)),
            ('team', self.gf('django.db.models.fields.related.ForeignKey')(to=orm['fkuniq.Team'])),
            ('number', self.gf('django.db.models.fields.PositiveIntegerField')()),
        ))
        db.send_create_signal('fkuniq', ['Player'])

        # Adding unique constraint on 'Player', fields ['team', 'number']
        db.create_unique('fkuniq_player', ['team_id', 'number'])

And in MySQL:

mysql> SHOW COLUMNS FROM fkuniq_player;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(11)          | NO   | PRI | NULL    | auto_increment |
| team_id | int(11)          | NO   | MUL | NULL    |                |
| number  | int(10) unsigned | NO   |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

I think south silently failed to create the unique constraint I wanted. In the Key column I see the primary key index on id and the foreign key index on team_id but there should also be a MUL in the number row because there should be a UNIQUE index on it with team_id. Also, removing the unique_together constraint from the model causes the next migration to fail with the error:

Traceback (most recent call last):
  ...
  File "/home/aogier/uniques/../uniques/fkuniq/migrations/0002_auto__del_unique_player_number_team.py", line 12, in forwards
    db.delete_unique('fkuniq_player', ['number', 'team_id'])
  File "/home/aogier/.virtualenvs/uniques/lib/python2.7/site-packages/south/db/generic.py", line 479, in delete_unique
    raise ValueError("Cannot find a UNIQUE constraint on table %s, columns %r" % (table_name, columns))
ValueError: Cannot find a UNIQUE constraint on table fkuniq_player, columns ['number', 'team_id']

I believe it is missing because MySQL doesn't play well when foreign key constraints and multi-column UNIQUE constraints coincide. There's a comment to that effect on the MySQL documentation for ALTER TABLE: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html (see about halfway down, comment by Hadi Rastgou).

Anyways, sorry for the long question: does anyone have a way to make this work? I'd love a clean way to do this within a migration, even if I have to write in a MySQL-specific query in raw SQL. Or maybe this is just flat-out impossible to do in MySQL, which would be good to know before I spend more time working on this.

Answer 1

Argh, fixed this myself. It's a known bug in south, fixed in their development branch. http://south.aeracode.org/ticket/747