Is there anyway to create unique TextField in django with mysql db backend?

Question

I create a model for multiple choice question. Each question has 5 choice of answers. And I need each question object to be unique based on it's question and answers. And so, I design the model like this.

from django.db import models


class MultipleChoiceQuestion(models.Model):
    ANSWERS = [('a', 'a'), ('b', 'b'), ('c', 'c'), ('d', 'd'), ('e', 'e')]
    question = models.TextField()
    a = models.TextField()
    b = models.TextField()
    c = models.TextField()
    d = models.TextField()
    e = models.TextField()
    true_answer = models.CharField(max_length=1, choices=ANSWERS)

    class Meta:
        unique_together = [('question', 'a', 'b', 'c', 'd', 'e')]

When I run migrate, mysql give this error:

1170, "BLOB/TEXT column 'question' used in key specification without a key length"

I found this error has been discussed here. But, I can't use CharField with it's small limit, because I need to store long text (until 10000 char or more).

sqlite3 and postgresql can do this (i mean django didn't complain about key specification for TEXT).

The reason I need to use mysql because the server where I will deploy this django app is only provide mysql, no postgresql.

So, is there anyway I could achieve this?

score 3 · Answer 1 · answered Oct 28 '15 at 05:45

It looks like this is a django/mysql bug where django blames MySql and "wontfix" it. Their suggestion is to leave the key off the model and just add the constraint manually. Huge hack but yes, that is probably the only solution. You will however need to recompile MySql if your key exceeds 1000 bytes.

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used. From The Manual

I don't suggest that for several reasons including performance and all around hackery. Instead of recompiling I suggest you create a hash field that is unique. This creates an md5 sum of all the fields and will always be 32 characters. The odds of duplicates are 1 in 2^128 so you are pretty safe.

from django.db import models
import hashlib


class MultipleChoiceQuestion(models.Model):
    ANSWERS = [('a', 'a'), ('b', 'b'), ('c', 'c'), ('d', 'd'), ('e', 'e')]
    question = models.TextField()
    a = models.TextField()
    b = models.TextField()
    c = models.TextField()
    d = models.TextField()
    e = models.TextField()
    true_answer = models.CharField(max_length=1, choices=ANSWERS)
    unique_hash = models.CharField(max_length=32, unique=True)

    def save(self, *args, **kwargs):
        m = hashlib.md5()
        m.update(self.question)
        m.update(self.a)
        m.update(self.b)
        m.update(self.c)
        m.update(self.d)
        m.update(self.e)
        self.unique_hash = m.digest()
        super(MultipleChoiceQuestion, self).save(*args, **kwargs)

You probably want to use `m.hexdigest()` as `m.digest()` will return an object like this: `b'\xd4\x1d\x8c\xd9\x8f\x00\xb2\x04\xe9\x80\t\x98\xec\xf8B~'` where as `m.hexdigest()` will return `d41d8cd98f00b204e9800998ecf8427e` — Blairg23, Dec 21 '18 at 00:52

Is there anyway to create unique TextField in django with mysql db backend?

1 Answers1