django.db.utils.IntegrityError: could not create unique index - DETAIL: Key (player)=(Lonergan) is duplicated. - without unique constraint in model

user_1746269 0
Viewed 3116

After a few commits offline including updating the model a couple of times and deployment to production the error message is: (It is working for development but failing in production)

django.db.utils.IntegrityError: could not create unique index - DETAIL:  Key (player)=(Lonergan) is duplicated.

It is over 100 players with a duplicate name in the table and the field player was never set to be unique. Why is this happening?

class Player(models.Model):
    player = models.CharField(max_length=50)
    team = models.ForeignKey(Team, related_name='players', on_delete=models.PROTECT)
    position = models.CharField(max_length=5)
    cost = models.FloatField()
    selection = models.FloatField()
    form = models.FloatField()
    points = models.IntegerField()
    lastgwscrape = models.DateTimeField(null=True)
    lastapiupdate = models.DateTimeField(null=True)
    currentgwdt = models.DateTimeField(null=True)
    apiid = models.IntegerField(null=True)

The apiid field was previously defined as unique and it was removed as a test to make this migration work.

class APIPlayerGW(models.Model):
    player = models.ForeignKey(Player, related_name='apigws', on_delete=models.CASCADE)
    gwid = models.IntegerField()
    points = models.IntegerField()
    minutesplayed = models.IntegerField()
    goalsscored = models.IntegerField()
    assists = models.IntegerField()
    cleansheets = models.IntegerField()
    goalsconceded = models.IntegerField()
    owngoals = models.IntegerField()
    penaltiessaved = models.IntegerField()
    penaltiesmissed = models.IntegerField()
    yellowcards = models.IntegerField()
    redcards = models.IntegerField()
    saves = models.IntegerField()
    bonuspoints = models.IntegerField()
    bonuspointsystem = models.IntegerField()
    influence = models.FloatField()
    creativity = models.FloatField()
    threat = models.FloatField()
    ictindex = models.FloatField()
    datetime = models.DateTimeField(default=timezone.now)
    season = models.CharField(max_length=10)

    # class Meta:
    #     unique_together = ('player','gwid','season') # Double gameweeks are lumped together

The above table had the unique_together combination that was commented out as a test to make this migration work. What can I do to make this migration work?

migration postgresql django-models django
3 Answers

From your remark:

The migration files had come out of sync with the migrations and Django was trying to use an old migration file.

I assume something like follows happened (or perhaps there may have been other scenarios too, like some migration files were manually edited, etc.):

  1. You had your project in some version control.
  2. Some people generated migrations locally and pushed to the version control (their names started with the same prefix e.g. 0003), which unfortunately wasn't detected, and perhaps more migrations were later generated on top of these ones.

I would suggest the following 3 ways to solve such issues:

1. Merge migrations using --merge

Generally the first thing one should try to fix such situations is to try using the --merge flag [Django docs] to merge the conflict. If the conflict is not very complex, in most cases Django will be able to fix it for you with this command.

2. Roll the migrations back and regenerate them

The next solution one should try (instead of making the migrations on the production server directly) is to fix the migrations locally by rolling back to some previous migration that is consistent with the production server's state. Suppose production is migrated properly till 0005, we will do something like follows (on the development server):

  1. Migrate back to 0005:

     python manage.py migrate <app_label> 0005

  2. Delete all migrations after 0005 OR selectively delete some migrations OR rename some migrations so that they are in order.

  3. Generate migrations again:

     python manage.py makemigrations

  4. Push these migrations to production and migrate.

3. Edit the migrations manually

If these steps are not feasible then one can try actually editing the migration files themselves to fix the problem by referring the documentation on Writing database migrations.

Note: Although your solution (fixing the migrations on production) works well, many people aren't allowed to do this, also this can lead to problems later on if we have multiple production servers since the migrations between them might not be consistent in such case.

user_149918640

Try using player__id in unique_together instead of player since its a ForeignKey so using just the id makes more sense. If it still does not work then ideally the ids of both the players are same.

user_60036770

The migration files had come out of sync with the migrations and Django was trying to use an old migration file. @Abdul Aziz Barkat's comment led me in the right direction. It was possible to solve this by resetting the Django migrations with the following steps:

  1. Comment out all changes in Models.py offline to reflect the production database's state
  2. Push to production
  3. Run python manage.py migrate --fake app_name zero
  4. Navigate to the migrations folder in your app in production and delete the old migration files with find . -path "*.py" -not -name "__init__.py" -delete and find . -path "*.pyc" -delete
  5. Create the new inital migration python manage.py makemigrations
  6. Since the database tables already exists run python manage.py migrate --fake-initial

I got an error message running with the --fake-initial flag, however replacing it with the --fake flag was working: python manage.py migrate --fake

user_17462690
Related