Making Django unique constraints case-insensitive (with no downtime)

Дата публикации: 08-03-2026 14:50:42

Fix Django’s case-sensitive unique constraint pitfalls by cleaning duplicates, adding Lower() constraints, and safely migrating with PostgreSQL CONCURRENTLY to avoid downtime.

Основное содержимое страницы с новостью.

In a recent project, I was building an auto parts catalog and ran into an interesting data integrity problem with unique constraints.

The problem#

Here's what my models looked like (CharField.max_length ommitted):

class Car(models.Model):
    make = models.CharField()
    model = models.CharField()
    # ...

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["make", "model"], name="unique_car_idx"
            ),
        ]

class Product(models.Model):
    name = models.CharField()
    compatible_cars = models.ManyToManyField("Car", related_name="products")
    # ...

The Car model represents unique vehicle definitions. Each combination of make and model should represent one specific car.

We create some entries:

peel_trident = Car.objects.create(make="Peel", model="Trident")

# Uh oh - someone adds the same car with different capitalization
peel_trident_lower = Car.objects.create(make="peel", model="trident")

# Add product to the correct/first car
wiper = Product.objects.create(name="Fancy tiny wiper")
peel_trident.products.add(wiper)

# Add another product to the duplicate
headlight = Product.objects.create(name="Halogen headlight")
headlight.compatible_cars.add(peel_trident_lower)

Oh no! We now have the Peel Trident saved twice with different capitalizations. Our unique constraint doesn't reflect the real-world requirement that car identifiers should be unique case-insensitively.

The solution#

A more appropriate constraint would use Django's Lower() function when creating the constraint:

from django.db.models.functions import Lower

class Car(models.Model):
    # ...

    class Meta:
        constraints = [
            models.UniqueConstraint(
                Lower("make"), Lower("model"), name="unique_car_lower_idx",
            ),
        ]

When we run makemigrations, Django generates a migration with operations like this:

operations = [
    migrations.RemoveConstraint(
        model_name='car', name='unique_car_idx'
    ),
    migrations.AddConstraint(
        model_name='car',
        constraint=models.UniqueConstraint(
            django.db.models.functions.text.Lower('make'),
            django.db.models.functions.text.Lower('model'),
            name='unique_car_lower_idx'
        ),
    ),
]

However, there is a critical problem with running this migration as-is in production. Of we try to run it, the migration will crash with an integrity error because of existing duplicates when it encounters our "Peel/Trident" entries.

Let's fix that.

Handling duplicates#

We need to clean up any existing case-insensitive duplicates before adding the new constraint. In this case, I chose to merge all products from duplicate cars into the first occurrence, then delete the extras.

I added a RunPython operation to handle this:

from django.db import transaction
from django.db.models import Count
from django.db.models.functions import Lower

def handle_duplicates(apps, schema_editor):
    Car = apps.get_model("autoparts", "Car")

    # Find all case-insensitive duplicates
    duplicates = (
        Car.objects.annotate(
            imake=Lower("make"), imodel=Lower("model")
        )
        .values("imake", "imodel")
        .annotate(count=Count("id"))
        .filter(count__gt=1)
    )

    with transaction.atomic():
        for dupe in duplicates:
            qs = Car.objects.filter(
                make__iexact=dupe["imake"], model__iexact=dupe["imodel"]
            ).order_by("id")

            cars = list(qs)
            first = cars[0]
            existing_products = set(first.products.values_list("id", flat=True))

            # Reassign products from duplicates to the first car
            for car in cars[1:]:
                products = [p for p in car.products.exclude(id__in=existing_products)]
                if products:
                    first.products.add(*products)

            # Delete all duplicates except the first
            qs.exclude(id=first.id).delete()

class Migration(migrations.Migration):
    # Dependencies

    operations = [
        migrations.RemoveConstraint(
            model_name='car', name='unique_car_idx'
        ),
        migrations.RunPython(
            handle_duplicates, reverse_code=migrations.RunPython.noop
        ),
        migrations.AddConstraint(
            model_name='car',
            constraint=models.UniqueConstraint(
                django.db.models.functions.text.Lower('make'),
                django.db.models.functions.text.Lower('model'),
                name='unique_car_lower_idx'
            ),
        ),
    ]

This solves the duplicate problem.

Avoiding table locks (PostgreSQL)#

There's one more improvement we can make. Creating an index is a locking operation — while the new index builds, the table is locked, potentially causing downtime for large tables in production.

Note: index creation only blocks writes, not reads. If your application has low write volume (like an internal tool used by a small team), you can simply coordinate a brief pause in writes during the migration and skip this section entirely. The following approach is valuable for high-traffic applications that can't afford to block writes.

PostgreSQL's CONCURRENTLY keyword lets us create indexes without locking. However, we can't use the standard AddConstraint operation for this. Instead, we use SeparateDatabaseAndState to run custom SQL for our model state changes.

class Migration(migrations.Migration):
    # dependencies = [...]

    operations = [
        migrations.RemoveConstraint(
            model_name='car', name='unique_car_idx'
        ),
        migrations.RunPython(
            handle_duplicates,
            reverse_code=migrations.RunPython.noop
        ),
        migrations.SeparateDatabaseAndState(
            state_operations=[
                migrations.AddConstraint(
                    model_name='car',
                    constraint=models.UniqueConstraint(
                        django.db.models.functions.text.Lower('make'),
                        django.db.models.functions.text.Lower('model'),
                        name='unique_car_lower_idx'
                    ),
                ),
            ],
            database_operations=[
                migrations.RunSQL(
                    sql="""
                    CREATE UNIQUE INDEX CONCURRENTLY unique_car_lower_idx
                    ON autoparts_car (
                        LOWER(make), LOWER(model)
                    );
                    """,
                    reverse_sql="DROP INDEX unique_car_lower_idx;"
                ),
            ],
        ),
    ]

If you try to run this migration, you'll hit an InternalError: CONCURRENTLY cannot be used inside a transaction, and Django migrations run in transactions by default.

The fix is simple — disable transactions for this migration:

class Migration(migrations.Migration):
    atomic = False  # Required for CONCURRENTLY

    dependencies = [
        ('autoparts', '0001_initial'),
    ]

    operations = [
        # ... rest of the operations
    ]

However, because we are no longer using an atomic transaction, deleting the existing index first creates a gap between the time when the existing index is removed and when a new one is added. Additionally, if our migration crashes after removing the index, but before completing the concurrent index creation, we will remain in a state where we have no index on the database, which can hurt performance and possibly result in downtime.

To ensure our database never reaches a state without a unique constraint, we should add the new constraint before removing the old one. This means reordering the operations:

class Migration(migrations.Migration):
    # ...
    operations = [
        # First, clean up duplicates
        migrations.RunPython(
            handle_duplicates, reverse_code=migrations.RunPython.noop
        ),
        # Add the new constraint (now possible since duplicates are gone)
        migrations.AddConstraint(
            model_name='car',
            constraint=models.UniqueConstraint(
                django.db.models.functions.text.Lower('make'),
                django.db.models.functions.text.Lower('model'),
                name='unique_car_lower_idx'
            ),
        ),
        # Only then remove the old constraint
        migrations.RemoveConstraint(
            model_name='car', name='unique_car_idx'
        ),
    ]

Now there's continuous constraint coverage throughout the migration.

This is safe because: - The RunPython operation defines its own transaction context for data cleanup - The new index is created first (without blocking writes due to CONCURRENTLY) - There's always at least one constraint in place - Dropping the old index doesn't require a transaction

Summary#

Migrating a unique constraint to be case-insensitive in production requires careful handling:

Handle duplicates first with a data migration that merges related data
Use CONCURRENTLY to avoid table locks on large databases
Disable transactions when using CONCURRENTLY

With these techniques, you can safely update your constraints without downtime or data integrity risks.

P.S. The database I was working with hadn't gone into production yet but I was inspired by Haki Benita's post to take up the challenge of writing production-ready migrations.

Схожие новости

#	Наименование новости	Информативность	Дата публикации
1	Django: introducing django-integrity-policy	35	03-06-2026
2	django-freeze: Convert Django Sites to Static Ones	24.29	23-04-2026
3	django-query-doctor: Diagnose Slow Django Queries	30	02-08-2026
4	manage.py migrate в пятницу в 17:30 на проде с 3K RPS и таблицей 200М строк	11.09	19-05-2026
5	django-hawkeye - BM25 full-text search using PostgreSQL	52.86	09-02-2026
6	django-arch-check: Static Checker for Common Django Issues	24.29	01-06-2026
7	Avoiding empty strings in non-nullable Django string-based model fields	12.43	20-03-2026
8	Avoiding empty strings in non-nullable Django string-based model fields	12.43	16-03-2026
9	Gotchas With SQLite in Production	12.11	03-04-2026
10	Что нужно знать о Django миграциях, чтобы не превратить в тыкву свой продакшен во время обновлений	7.16	24-02-2026

Классификация: . Схожих патентов: 0. Схожих новостей: 10. Тональность: 0. Информативность: 14.22. Источник: pythondigest.ru.

Вход на сайт

Просмотр новости

Найдите то, что Вас интересует

Making Django unique constraints case-insensitive (with no downtime)

Дата публикации: 08-03-2026 14:50:42

Основное содержимое страницы с новостью.

Схожие новости