django.db.utils.IntegrityError: duplicate key value violates unique constraint "django_migrations_pkey"

Question

I have a Django model SessionType which is defined similar to the following:

from django import models


class SessionType(models.Model):
    class Meta:
        ordering = ['title']
    title = models.CharField(max_length=255, unique=True)

At first, the unique=True constraint was not there; I've just added it, and run python manage.py makemigrations. This resulted in the following migration (0163_auto_20180627_1309.py):

# -*- coding: utf-8 -*-
# Generated by Django 1.11.9 on 2018-06-27 20:09
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('lucy_web', '0162_merge_20180531_0009'),
    ]

    operations = [
        migrations.AlterField(
            model_name='sessiontype',
            name='title',
            field=models.CharField(max_length=255, unique=True),
        ),
    ]

However, when I try to python manage.py migrate, I get the following error:

django.db.utils.IntegrityError: duplicate key value violates unique constraint "django_migrations_pkey" DETAIL: Key (id)=(326) already exists.

Here are the commands and the full traceback:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py makemigrations
Migrations for 'lucy_web':
  lucy_web/migrations/0163_auto_20180627_1309.py
    - Alter field title on sessiontype
(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auditlog, auth, contenttypes, defender, lucy_web, oauth2_provider, otp_static, otp_totp, sessions, two_factor
Running migrations:
  Applying lucy_web.0163_auto_20180627_1309...Traceback (most recent call last):
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.IntegrityError: duplicate key value violates unique constraint "django_migrations_pkey"
DETAIL:  Key (id)=(326) already exists.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 28, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 356, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 250, in apply_migration
    self.recorder.record_applied(migration.app_label, migration.name)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/recorder.py", line 73, in record_applied
    self.migration_qs.create(app=app, name=name)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/models/query.py", line 394, in create
    obj.save(force_insert=True, using=self.db)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/models/base.py", line 808, in save
    force_update=force_update, update_fields=update_fields)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/models/base.py", line 838, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/models/base.py", line 924, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/models/base.py", line 963, in _do_insert
    using=using, raw=raw)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/models/query.py", line 1076, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1112, in execute_sql
    cursor.execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "django_migrations_pkey"
DETAIL:  Key (id)=(326) already exists.

Looking at the database, I see there is another migration with this primary key of 326:

That migration, 0161_auto_20180530_2140.py, also contains AlterField operations on the SessionType model:

# -*- coding: utf-8 -*-
# Generated by Django 1.11.9 on 2018-05-31 04:40
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('lucy_web', '0160_merge_20180524_1507'),
    ]

    operations = [
        migrations.AlterField(
            model_name='sessiontype',
            name='description',
            field=models.TextField(blank=True),
        ),
        migrations.AlterField(
            model_name='sessiontype',
            name='short_description',
            field=models.TextField(blank=True),
        ),
    ]

Following django db migration failed with postgres, I've tried to run the command

ALTER SEQUENCE django_migrations_id_seq RESTART WITH 329;

like so:

However, now when I try to migrate I run into another error that a certain unique constraint already exists:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auditlog, auth, contenttypes, defender, lucy_web, oauth2_provider, otp_static, otp_totp, sessions, two_factor
Running migrations:
  Applying lucy_web.0163_auto_20180627_1309...Traceback (most recent call last):
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: relation "lucy_web_sessiontype_title_c207e4f8_uniq" already exists


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 28, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 356, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/migration.py", line 129, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/operations/fields.py", line 221, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 515, in alter_field
    old_db_params, new_db_params, strict)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/postgresql/schema.py", line 112, in _alter_field
    new_db_params, strict,
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 719, in _alter_field
    self.execute(self._create_unique_sql(model, [new_field.column]))
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: relation "lucy_web_sessiontype_title_c207e4f8_uniq" already exists

How shall I solve this? Should I just delete that unique constraint and re-run migrations?

Answer 1

This fix is working for my postgreSQL db

Open django shell

python manage.py shell

Run the following Python code to reset id

from django.db import connections

query = "SELECT setval('django_migrations_id_seq', (SELECT MAX(id) FROM django_migrations))"
cursor = connections['default'].cursor()
cursor.execute(query) 
row = cursor.fetchone()

Answer 2

Run the migrate command repeatedly until the error goes. On each try it tries to increment the id(pk) value. Once the increment reaches out the max of existing id, the command runs successfully.

Answer 3

After restarting the index, I indeed simply deleted/dropped the constraints and indices that were giving "already exists" errors and managed to migrate:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auditlog, auth, contenttypes, defender, lucy_web, oauth2_provider, otp_static, otp_totp, sessions, two_factor
Running migrations:
  Applying lucy_web.0163_auto_20180627_1309... OK