Django: Model UniqueConstraint gives an error when migrating

Question

In my Django project the in one of the model I need to use two UniqueConstraint instances. But when I add that and do the migration after running makemigrations it gives an error in the terminal.

Model class:

class MyDays(models.Model):
    class Meta:
        verbose_name = "My Day"
        verbose_name_plural = "My Days"
        constraints = [
            models.UniqueConstraint(fields=['userid', 'date', 'from'], condition=Q(status=1), name='user_date_from_a'),
            models.UniqueConstraint(fields=['userid', 'date', 'to'], condition=Q(status=1), name='user_date_to_b')
        ]

    id = models.BigAutoField(primary_key=True)
    userid = models.ForeignKey(User, on_delete=models.CASCADE, verbose_name="User")
    date = models.DateField(auto_now_add=False, editable=True, validators=[validate_date])
    from = models.TimeField(auto_now_add=False, editable=True)
    to = models.TimeField(auto_now_add=False, editable=True)
    status = models.ForeignKey(Status, on_delete=models.CASCADE, verbose_name="Status", default=1)

When I run python3 manage.py migrate, it gives the following error:

django.core.exceptions.FieldError: Joined field references are not permitted in this query

I need to have unique records only if the status is 1 along with the other 3 field combination. What am I doing wrong? How can I fix this error?

Can you show the full traceback? Are you sure the error is about *this* `UniqueConstraint`. — Willem Van Onsem, Jan 16 '20 at 08:06
@WillemVanOnsem Yes. It appears with it. When I revert the model back to where it had only unique_together and removing the newly generated migration files it works fine. When I add these UniqueConstraint I get this error. — Teshan N., Jan 16 '20 at 08:53
this might help https://stackoverflow.com/questions/4440010/django-unique-together-with-foreign-keys — asad_hussain, Jan 16 '20 at 10:53

Jonathan Richards · Answer 1 · 2020-01-19T04:37:14.243

Try fields=['userid_id'...

It looks like the UniqueConstraints are trying to join across to the user table.

Django takes your ForeignKeys and OneToOneFields and adds _id to the end to create the DB column storing the related object's primary key (usually an integer). You generally shouldn't put "id" on the end of related object names for that reason.

So if my_days is an instance of MyDays, then my_days.userid is a user instance and my_days.userid_id is (usually) an integer.

If the behaviour is different with unique_together, that might be considered a bug in UniqueConstraint.

Django: Model UniqueConstraint gives an error when migrating

1 Answers1

Linked