"there is no unique constraint matching given keys" migration erro

Using Django Mystery Errors
1

Hi all, I have a charfield that I am replacing with a ManyToMany relationship - it was previously an unimplemented feature so there is no data to migrate, all I have to do is rip out the old field and create the new one, this is the migration:

# Generated by Django 4.2.11 on 2024-05-18 21:20

from django.db import migrations, models


class Migration(migrations.Migration):
    dependencies = [
        ('core', '0073_alter_vulnerability_cvss_score'),
    ]

    operations = [
        migrations.RemoveField(
            model_name='product',
            name='certifying_entity',
        ),
        migrations.AddField(
            model_name='product',
            name='certifying_entities',
            field=models.ManyToManyField(
                related_name='certified_products', to='core.organization'
            ),
        ),
    ]

However, when I apply this migration, I am getting a psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "core_product" followed by a django.db.utils.ProgrammingError: there is no unique constraint matching given keys for referenced table "core_product" (see full log trace below)…

What I don’t understand here is: This is a ManyToMany, so I don’t want there to be unique constraint anyways, right? I fundamentally don’t understand this error and have spent hours staring at it without making any progress… Any pointers appreciated!

2024-05-28T05:10:52.511827586Z Running migrations:
2024-05-28T05:10:53.432308225Z   Applying core.0074_remove_product_certifying_entity_and_more...2024-05-28T05:10:50.538598675Z PostgreSQL is available
2024-05-28T05:10:52.903138745Z Traceback (most recent call last):
2024-05-28T05:10:52.903932602Z   File "/opt/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
2024-05-28T05:10:52.905109527Z     return self.cursor.execute(sql)
2024-05-28T05:10:52.905324191Z            ^^^^^^^^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.905356278Z psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "core_product"
2024-05-28T05:10:52.905367478Z 
2024-05-28T05:10:52.905395859Z 
2024-05-28T05:10:52.905433705Z The above exception was the direct cause of the following exception:
2024-05-28T05:10:52.905459499Z 
2024-05-28T05:10:52.905472944Z Traceback (most recent call last):
2024-05-28T05:10:52.905987212Z   File "/app/manage.py", line 22, in <module>
2024-05-28T05:10:52.906008023Z     main()
2024-05-28T05:10:52.906017749Z   File "/app/manage.py", line 18, in main
2024-05-28T05:10:52.906024526Z     execute_from_command_line(sys.argv)
2024-05-28T05:10:52.906062211Z   File "/opt/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
2024-05-28T05:10:52.906512925Z     utility.execute()
2024-05-28T05:10:52.906552043Z   File "/opt/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute
2024-05-28T05:10:52.907072181Z     self.fetch_command(subcommand).run_from_argv(self.argv)
2024-05-28T05:10:52.907089302Z   File "/opt/venv/lib/python3.11/site-packages/django/core/management/base.py", line 412, in run_from_argv
2024-05-28T05:10:52.907134981Z     self.execute(*args, **cmd_options)
2024-05-28T05:10:52.907154320Z   File "/opt/venv/lib/python3.11/site-packages/django/core/management/base.py", line 458, in execute
2024-05-28T05:10:52.907590782Z     output = self.handle(*args, **options)
2024-05-28T05:10:52.907609231Z              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.907634900Z   File "/opt/venv/lib/python3.11/site-packages/django/core/management/base.py", line 106, in wrapper
2024-05-28T05:10:52.907673498Z     res = handle_func(*args, **kwargs)
2024-05-28T05:10:52.907691287Z           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.907737565Z   File "/opt/venv/lib/python3.11/site-packages/django/core/management/commands/migrate.py", line 356, in handle
2024-05-28T05:10:52.908398390Z     post_migrate_state = executor.migrate(
2024-05-28T05:10:52.908432707Z                          ^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.908442379Z   File "/opt/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 135, in migrate
2024-05-28T05:10:52.908451293Z     state = self._migrate_all_forwards(
2024-05-28T05:10:52.908458659Z             ^^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.908464816Z   File "/opt/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
2024-05-28T05:10:52.908503009Z     state = self.apply_migration(
2024-05-28T05:10:52.908511305Z             ^^^^^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.908531760Z   File "/opt/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 249, in apply_migration
2024-05-28T05:10:52.909366988Z     with self.connection.schema_editor(
2024-05-28T05:10:52.909383278Z   File "/opt/venv/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 166, in __exit__
2024-05-28T05:10:52.909391528Z     self.execute(sql)
2024-05-28T05:10:52.909399896Z   File "/opt/venv/lib/python3.11/site-packages/django/db/backends/postgresql/schema.py", line 48, in execute
2024-05-28T05:10:52.909407482Z     return super().execute(sql, None)
2024-05-28T05:10:52.909413747Z            ^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.909424568Z   File "/opt/venv/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 201, in execute
2024-05-28T05:10:52.909431831Z     cursor.execute(sql, params)
2024-05-28T05:10:52.909473560Z   File "/opt/venv/lib/python3.11/site-packages/sentry_sdk/integrations/django/__init__.py", line 644, in execute
2024-05-28T05:10:52.909676780Z     result = real_execute(self, sql, params)
2024-05-28T05:10:52.909735685Z              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.909757131Z   File "/opt/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
2024-05-28T05:10:52.909867563Z     return self._execute_with_wrappers(
2024-05-28T05:10:52.909898228Z            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.909906756Z   File "/opt/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
2024-05-28T05:10:52.910018480Z     return executor(sql, params, many, context)
2024-05-28T05:10:52.910093077Z            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.910103142Z   File "/opt/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
2024-05-28T05:10:52.910222856Z     with self.db.wrap_database_errors:
2024-05-28T05:10:52.910268688Z   File "/opt/venv/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
2024-05-28T05:10:52.911830531Z     raise dj_exc_value.with_traceback(traceback) from exc_value
2024-05-28T05:10:52.911847399Z   File "/opt/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
2024-05-28T05:10:52.911855551Z     return self.cursor.execute(sql)
2024-05-28T05:10:52.911862767Z            ^^^^^^^^^^^^^^^^^^^^^^^^
2024-05-28T05:10:52.911869584Z django.db.utils.ProgrammingError: there is no unique constraint matching given keys for referenced table "core_product"
2024-05-28T05:10:52.911877002Z 
2024-05-28T05:10:53.011854931Z Sentry is attempting to send 2 pending events
2024-05-28T05:10:53.011929953Z Waiting up to 2 seconds
2024-05-28T05:10:53.011943393Z Press Ctrl-C to quit
2

Please show the ManyToManyField definition in your Product model.

<conjecture>
Trying to do both steps in the same migration is confusing Django.

I’d try either breaking this down into two separate migration steps (first remove the old field, run makemigrations, then add the new field followed by another makemigrations), or, if that doesn’t work, add the new field with a different name, remove the original, then rename that new field.
</conjecture>

3

Thank you for taking a look at this, Ken!

My Product class is very lengthy, but the relevant field definition is:


class Product(DependencyModelNode):
    # ... many more fields ...
    certifying_entities = models.ManyToManyField(
        to='Organization', related_name='certified_products'
    )

So the thing is, I have a “dev” and a “beta” environment, and this migration went through successfully on dev and is now breaking on beta (which has slightly different code in other places, but the same exact definition of Product and Organization…)

So, if doing this in the same migration is problematic and breaking them out could solve it, how do I break it out into two migrations without causing future inconsistent migration issues on the dev environments which is already at 0075 in terms of migration history?

4

You at least two options that I can think of off-hand - there’s the “clean” way and the “dirty” way.

“Clean” way:

  • Back out this migration.
  • Delete the backed-out migration.
  • Follow the steps above.

“Dirty” way:

  • Remove the second step from the migration that has been applied.
  • Create a new migration for the add step that was removed above.
  • Apply the new migration with --fake to make your test system aware that this migration has already been applied.
1 Like