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

Question

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?

Answer 1

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.