How to add unique constraint to big textfield in django?

Question

models.py:

class PageURL(models.Model):
    url = models.TextField(max_length=2048, unique=True)

Error during migrations:

django.db.utils.OperationalError: (1170, "BLOB/TEXT column 'url' used in key specification without a key length")

I considered a CharField but Django said the max on that for this use case was 255 characters. Initially I just had TextField(unique=True) but decided to add a max_length which I heard was normally 2048 for URLs but that hasn't helped silence the errors.

I tried CharField with no max_length and just a unique constraint set.

core.PageURL.url: (fields.E120) CharFields must define a 'max_length' attribute. core.PageURL.url: (mysql.E001) MySQL does not allow unique CharFields to have a max_length > 255.

The solution I've rigged is to go into phpMyAdmin and alter the structure by hand to varchar(2048) and add a unique constraint to it.

Lord Elrond · Answer 1 · 2019-09-30T03:25:44.310

Unique constraints aren't possible with a MySQL BLOB/TEXT column. See this question for a detailed explanation on that.

To address the migration lock you are in, you will need to rollback your migration to the point before you tried to add a unique=True constraint to the url field. Look through myapp/migrations for this model, and see when that was exactly. The file should look something like this:

class Migration(migrations.Migration):

    dependencies = [
        (<myapp>, <migration name>),
    ]

    operations = [
        migrations.AlterField(
            model_name='PageURL',
            name='url',
            field=models.TextField(max_length=2048, unique=True),
        ),
    ]

Now, from the command line, run python3 manage.py migrate <myapp> <migration name>

Lastly, delete all the migrations that came after <migration name>.

Edit

I figured I should at least attempt to give you a solution for your specific case, although I don't know what that model is used for exactly.

If you want to have a unique url column, you can try a FK relation like this:

class Url(models.Model):
    protocol = models.CharField(max_length=10)
    sld = models.CharField(max_length=2048, unique=True)
    tld = models.CharField(max_length=10)

    class Meta:
        unique_together = (('protocol', 'sld', 'tld'),)

    def __str__(self):
        return f'{self.protocol}://{self.sld}.{self.tld}'

Now on any models that need a url column, you can do something like this instead:

class SomeModel(models.Model):
    url = models.ForeignKey(Url, related_name='url_path', on_delete=models.PROTECT)
    url_path = models.CharField(max_length=255)

    def get_url(self):
        return f'{self.url}/{self.url_path}'

How to add unique constraint to big textfield in django?

1 Answers1

Edit