[FIXED] UniqueConstraint and ignore case sensitive

Issue

I would like to use this code:

constraints = [
    models.UniqueConstraint(fields=['name', 'app'], name='unique_booking'),
]

but name and app (both of them) should not check case sensitive so "FOo" and "fOO" should be marked as the same app. How to do it?

Solution

There are 2 options:

First Option:

In case you are using postgresql and Django version which is later than 2.0 - you can use CICharField or CITextField fields.

Your model will look like that:

class DemoModelCI(models.Model):

    class Meta(object):
        constraints = [
            UniqueConstraint(fields=['app', 'name'], name='unique_booking_ci')
        ]

    name = CICharField(null=False, default=None, max_length=550, blank=False)
    app = CICharField(null=False, default=None, max_length=550, blank=False)

Let's check using the shell:

Note the following:

• Working with the CI fields has some limitation if you work with multiple languages. Read documentation before using it.

• To work with CIText you need to create the extension on your DB. You can do it in a Django migration:

from django.db import migrations
from django.contrib.postgres.operations import CITextExtension

class Migration(migrations.Migration):

    dependencies = [
        ('storage', '0037_previous_migration'),
    ]

    operations = [
        CITextExtension(),
    ]

Second Option:

Create 2 fields for each of the case insensitive fields. The first will be for display and to maintain the original case of the text. The second will be saved lowercased and will be used for checking the uniqueness.

Your model will look like:

class DemoModel(models.Model):


    class Meta(object):
        constraints = [
            UniqueConstraint(fields=['name_compare', 'app_compare'], name='unique_booking')
        ]

    name_display = models.TextField(null=False, default=None, blank=False, max_length=550)
    name_compare = models.TextField(null=False, default=None, blank=False, max_length=550)

    app_display = models.TextField(null=False, default=None, blank=False, max_length=550)
    app_compare = models.TextField(null=False, default=None, blank=False, max_length=550)

    def save(self, *args, **kwargs):
        # If explicitly received "compare" name - make sure it is casefolded
        if self.name_compare:
            self.name_compare = self.name_compare.casefold()
        # If didn't get "compare" name - use the display name and casefold it
        elif self.name_display:
            self.name_compare = self.name_display.casefold()

        # If explicitly received "compare" name - make sure it is casefolded
        if self.app_compare:
            self.app_compare = self.app_compare.casefold()
        # If didn't get "compare" name - use the display name and casefold it
        elif self.app_display:
            self.app_compare = self.app_display.casefold()

        return super(DemoModel, self).save(*args, **kwargs)

Let's check using the shell:

Note the following:

• Read about "casefold" vs "lower" to decide which is better for your case.

Answered By - Adi Gabaie