Is there a way to create a unique id over 2 fields?

Question

Here is my model:

class GroupedModels(models.Model):
    other_model_one = models.ForeignKey('app.other_model')
    other_model_two = models.ForeignKey('app.other_model')

Essentially, what I want is is for other_model to be unique in this table. That means that if there is a record where other_model_one id is 123, I should not allow another record to be created with other_model_two id as 123. I can override clean I guess but I was wondering if django has something built in.

I am using version 2.2.5 with PSQL.

Edit: This is not a unqiue together situation. If I add a record with other_model_one_id=1 and other other_model_two_id=2, I should not be able to add another record with other_model_one_id=2 and other other_model_two_id=1

Answer 1

I explain several options here, maybe one of them or a combination can be useful for you.

Overriding save

Your constraint is a business rule, you can override save method to keep data consistent:

class GroupedModels(models.Model): 
    # ...
    def clean(self):
        if (self.other_model_one.pk == self.other_model_two.pk):
            raise ValidationError({'other_model_one':'Some message'}) 
        if (self.other_model_one.pk < self.other_model_two.pk):
            #switching models
            self.other_model_one, self.other_model_two = self.other_model_two, self.other_model_one
    # ...
    def save(self, *args, **kwargs):
        self.clean()
        super(GroupedModels, self).save(*args, **kwargs)

Change design

I put a sample easy to understand. Let's suppose this scenario:

class BasketballMatch(models.Model):
    local = models.ForeignKey('app.team')
    visitor = models.ForeignKey('app.team')

Now, you want to avoid a team playing a match with itself also team A only can play with team B for once (almost your rules). You can redesign your models as:

class BasketballMatch(models.Model):
    HOME = 'H'
    GUEST = 'G'
    ROLES = [
        (HOME, 'Home'),
        (GUEST, 'Guest'),
    ]
    match_id = models.IntegerField()
    role = models.CharField(max_length=1, choices=ROLES)
    player = models.ForeignKey('app.other_model')

    class Meta:
      unique_together = [ ( 'match_id', 'role', ) ,
                          ( 'match_id', 'player',) , ]

ManyToManyField.symmetrical

This look like a symetrical issue, django can handle it for you. Instead of create GroupedModels model, just make a ManyToManyField field with itself on OtherModel:

from django.db import models
class OtherModel(models.Model):
    ...
    grouped_models = models.ManyToManyField("self")

This is what django has as built in for these scenarios.

Answer 2

It's not a very satisfying answer, but unfortunately the truth is there is no way to do what you're describing with a simple built-in feature.

What you described with clean would work, but you have to be careful to manually call it as I think it's only automatically called when using ModelForm. You might be able to create a complex database constraint but that would live outside of Django and you'd have to handle database exceptions (which can be difficult in Django when in the middle of a transaction).

Maybe there's a better way to structure the data?

Answer 3

There is already a great answer from dani herrera, however I wish to further elaborate on it.

As explained in the second option, the solution as required by the OP is to change the design and implement two unique constraints pairwise. The analogy with the basketball matches illustrates the problem in a very practical way.

Instead of a basketball match, I use example with football (or soccer) games. A football game (which I call it Event) is played by two teams (in my models a team is Competitor). This is a many-to-many relation (m:n), with n limited to two in this particular case, the principle is suitable for an unlimited number.

Here is how our models look:

class Competitor(models.Model):
    name = models.CharField(max_length=100)
    city = models.CharField(max_length=100)

    def __str__(self):
        return self.name


class Event(models.Model):
    title = models.CharField(max_length=200)
    venue = models.CharField(max_length=100)
    time = models.DateTimeField()
    participants = models.ManyToManyField(Competitor)

    def __str__(self):
        return self.title

An event could be:

• title: Carabao Cup, 4th round,
• venue: Anfield
• time: 30. October 2019, 19:30 GMT
• participants:
  • name: Liverpool, city: Liverpool
  • name: Arsenal, city: London

Now we have to solve the issue from the question. Django automatically creates an intermediate table between the models with a many-to-many relation, but we can use a custom model and add further fields. I call that model Participant:

class Participant(models.Model):
    ROLES = (
        ('H', 'Home'),
        ('V', 'Visitor'),
    )
    event = models.ForeignKey(Event, on_delete=models.CASCADE)
    competitor = models.ForeignKey(Competitor, on_delete=models.CASCADE)
    role = models.CharField(max_length=1, choices=ROLES)

    class Meta:
        unique_together = (
            ('event', 'role'),
            ('event', 'competitor'),
        )

    def __str__(self):
        return '{} - {}'.format(self.event, self.get_role_display())

The ManyToManyField has an option through that allows us to specify the intermediate model. Let's change that in the model Event:

class Event(models.Model):
    title = models.CharField(max_length=200)
    venue = models.CharField(max_length=100)
    time = models.DateTimeField()
    participants = models.ManyToManyField(
        Competitor,
        related_name='events', # if we want to retrieve events for a competitor
        through='Participant'
    )

    def __str__(self):
        return self.title

The unique constraints now will automatically limit the number of competitors per event to two (because there are only two roles: Home and Visitor).

In a particular event (football game) there can be only one home team and only one visitor team. A club (Competitor) can appear either as home team or as visitor team.

How do we manage now all these things in the admin? Like this:

from django.contrib import admin

from .models import Competitor, Event, Participant


class ParticipantInline(admin.StackedInline): # or admin.TabularInline
    model = Participant
    max_num = 2


class CompetitorAdmin(admin.ModelAdmin):
    fields = ('name', 'city',)


class EventAdmin(admin.ModelAdmin):
    fields = ('title', 'venue', 'time',)
    inlines = [ParticipantInline]


admin.site.register(Competitor, CompetitorAdmin)
admin.site.register(Event, EventAdmin)

We have added the Participant as inline in the EventAdmin. When we create new Event we can choose the home team and the visitor team. The option max_num limits the number of entries to 2, therefore no more then 2 teams can be added per event.

This can be refactored for a different use cases. Let's say our events are swimming competitions and instead of home and visitor, we have lanes 1 to 8. We just refactor the Participant:

class Participant(models.Model):
    ROLES = (
        ('L1', 'lane 1'),
        ('L2', 'lane 2'),
        # ... L3 to L8
    )
    event = models.ForeignKey(Event, on_delete=models.CASCADE)
    competitor = models.ForeignKey(Competitor, on_delete=models.CASCADE)
    role = models.CharField(max_length=1, choices=ROLES)

    class Meta:
        unique_together = (
            ('event', 'role'),
            ('event', 'competitor'),
        )

    def __str__(self):
        return '{} - {}'.format(self.event, self.get_role_display())

With this modification we can have this event:

• title: FINA 2019, 50m backstroke men's final,

  • venue: Nambu University Municipal Aquatics Center
  • time: 28. July 2019, 20:02 UTC+9

  • participants:

    • name: Michael Andrew, city: Edina, USA, role: lane 1
    • name: Zane Waddell, city: Bloemfontein, South Africa, role: lane 2
    • name: Evgeny Rylov, city: Novotroitsk, Russia, role: lane 3
    • name: Kliment Kolesnikov, city: Moscow, Russia, role: lane 4

    // and so on lane 5 to lane 8 (source: Wikipedia

A swimmer can appear only once in a heat, and a lane can be occupied only once in a heat.

I put the code to GitHub: https://github.com/cezar77/competition.

Again, all credits go to dani herrera. I hope this answer provides some added value to the readers.