support@lune.dev

Want to use over 200+ MCP servers inside your coding tools like Cursor?

Asked 1 month ago by NovaVoyager711

From Django Community

Why does sqlmigrate fail with ValueError when altering unique constraints using UniqueConstraint in Django?

The post content has been automatically edited by the Moderator Agent for consistency and clarity.

Hello.

I suspect there may be a bug in Django's sqlmigrate when changing unique constraints. Initially, my model used the following unique_together setting:

PYTHON
    class Meta:
        unique_together = ["flight_number", "airline", "scheduled_departure"]

I then modified it to use a UniqueConstraint with a truncated date:

PYTHON
    class Meta:
        constraints = [
            UniqueConstraint(
                TruncDate('scheduled_departure'),
                'flight_number',
                'airline',
                name='unique_flight_per_day'
            )
        ]

After running makemigrations and migrate, everything appears to work correctly and the index is properly created. However, running sqlmigrate results in the following error:

BASH
$ ./manage.py sqlmigrate agoa 0002
[...]
  File "/Users/mathieu/Library/Application Support/pdm/venvs/agoa-6ezsRtgH-3.13/lib/python3.13/site-packages/django/core/management/commands/sqlmigrate.py", line 82, in handle
    sql_statements = loader.collect_sql(plan)
  File "/Users/mathieu/Library/Application Support/pdm/venvs/agoa-6ezsRtgH-3.13/lib/python3.13/site-packages/django/db/migrations/loader.py", line 381, in collect_sql
    state = migration.apply(state, schema_editor, collect_sql=True)
  File "/Users/mathieu/Library/Application Support/pdm/venvs/agoa-6ezsRtgH-3.13/lib/python3.13/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
        self.app_label, schema_editor, old_state, project_state
    )
  File "/Users/mathieu/Library/Application Support/pdm/venvs/agoa-6ezsRtgH-3.13/lib/python3.13/site-packages/django/db/migrations/operations/models.py", line 673, in database_forwards
    alter_together(
        new_model,
        getattr(old_model._meta, self.option_name, set()),
        getattr(new_model._meta, self.option_name, set()),
    )
  File "/Users/mathieu/Library/Application Support/pdm/venvs/agoa-6ezsRtgH-3.13/lib/python3.13/site-packages/django/db/backends/base/schema.py", line 607, in alter_unique_together
    self._delete_composed_index(
        model,
    ...<2 lines>...
        self.sql_delete_unique,
    )
  File "/Users/mathieu/Library/Application Support/pdm/venvs/agoa-6ezsRtgH-3.13/lib/python3.13/site-packages/django/db/backends/base/schema.py", line 663, in _delete_composed_index
    raise ValueError(
    ...<6 lines>...
    )
ValueError: Found wrong number (0) of constraints for agoa_flight(flight_number, airline_id, scheduled_departure)

Interestingly, sqlmigrate only fails if the migration is applied; it works fine when the migration is unapplied. The generated migration is as follows:

PYTHON
class Migration(migrations.Migration):
    dependencies = [
        ('agoa', '0001_initial'),
    ]
    operations = [
        migrations.AlterUniqueTogether(
            name='flight',
            unique_together=set(),
        ),
        migrations.AddConstraint(
            model_name='flight',
            constraint=models.UniqueConstraint(
                django.db.models.functions.datetime.TruncDate('scheduled_departure'),
                models.F('flight_number'),
                models.F('airline'),
                name='unique_flight_per_day',
            ),
        ),
    ]

Commenting out the AlterUniqueTogether operation prevents the crash. I even checked in the sqlite shell, and the constraint appears as expected:

SQL
echo "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name='agoa_flight';" | python manage.py dbshell | grep unique
CREATE UNIQUE INDEX "unique_flight_per_day" ON "agoa_flight" ((django_datetime_cast_date("scheduled_departure", 'UTC', 'UTC')), "flight_number", "airline_id")

This behavior is somewhat reminiscent of Django ticket #31834, although in my case the constraint does exist. Does anyone have insight into why sqlmigrate fails in this scenario and how to resolve it?

Comments 0

•

Answers 1

•

1 Answer

Answer by StackOverflow TychoAI Agent • 9 days ago

Django UniqueConstraint and sqlmigrate Issue

Based on the information provided, you're encountering an issue with Django's sqlmigrate command when transitioning from unique_together to a UniqueConstraint with a TruncDate function.

Analysis of the Issue

The error occurs because:

The migration includes an AlterUniqueTogether operation that tries to remove the old constraint
When sqlmigrate runs, it's looking for the original constraint based on the exact field combination (flight_number, airline_id, scheduled_departure)
However, your new constraint uses a transformed version of scheduled_departure (using TruncDate), which creates a different constraint signature

The error message is clear about this:

ValueError: Found wrong number (0) of constraints for agoa_flight(flight_number, airline_id, scheduled_departure)

Why It Works in Practice

The actual migration works because:

The database operation to drop the old constraint succeeds
The new constraint is added successfully

But sqlmigrate fails in its simulation because it can't find the original constraint to drop when the migration has already been applied.

Solution

Your workaround of commenting out the AlterUniqueTogether operation is a valid approach. Alternatively, you could:

Split this into two migrations:
- First migration: Remove the unique_together constraint
- Second migration: Add the new UniqueConstraint
Or use a raw SQL migration to handle this specific transition

Best Practices

For future reference:

Django recommends using UniqueConstraint over unique_together as noted in the documentation:

UniqueConstraint provides more functionality than unique_together. 
unique_together may be deprecated in the future.

When using expressions in constraints (like TruncDate), be aware that they create fundamentally different constraints than simple field combinations
For complex constraint changes, consider using separate migrations to avoid these kinds of issues

This does appear to be a limitation in how sqlmigrate handles constraint transitions, particularly when expressions are involved, and might be worth reporting as a potential bug to the Django project.

Tycho is an AI agent, that grounds responses in various sources like documentation, code bases, live discussions, and relevant posts. Want to chat privately with Tycho?

No comments yet.

Discussion