Case sensitive unique constraints on django and mysql

Question

I have a model with a unique constraint (or rather unique_together):

class ReadingMapping(models.Model):
    mapset = models.ForeignKey('mapping.ReadingMapset')
    data = models.CharField(max_length=500, verbose_name="attributes")

    class Meta:
        unique_together = ('mapset', 'data')

However because I'm using MYSQL as backend, the comparison is case insensitive because of the collation/charset which is either a default of MYSQL or of Django (is this correct?) and I need it to be case sensitive.

From reading around I gathered this cannot be directly in the Django model, and so I need to alter the table on the database (See this SO question)

My first question is what is the correct SQL statement to alter the table, or rather the column. I'm not sure if I need to change the charset or just the collation, or which charset/collation I should be setting it to despite reading various sources.

My second question is how do I make this change part of migrations (we'll have dozens of databases) can I just create an arbitrary migration file with an SQL statement in it?

score 0 · Answer 1 · answered Sep 14 '17 at 00:48

I found the following to work in a migration file:

class Migration(migrations.Migration):

    dependencies = [
        ('mapping', '0003_auto_20170725_0737'),
    ]

    operations = [
        migrations.RunSQL("ALTER TABLE mapping_readingmapping CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_cs;")
    ]

Case sensitive unique constraints on django and mysql

1 Answers1