Apply unique constraint to columns on two separate tables (Django, postgres)

Question

So I'm putting together a site which works a little like github, where both users and organisations have a handle:

class User(AbstractUser):
    """ Using django's default user model, with its username field which looks like:

        username = models.CharField(
            _('username'),
            max_length=150,
            unique=True,
            help_text=_('Required. 150 characters or fewer. Letters, digits and @/./+/-/_ only.'),
            validators=[username_validator],
            error_messages={
                'unique': _("A user with that username already exists."),
            },
        )
    """

    # Many users to many organisations
    organisations = ManyToManyField('Organisation', related_name='users', blank=True)

class Organisation(models.Model):

    # ...other stuff, id etc
    handle = CharField(unique=True, max_length=36, null=False)

I'll set up urls with endpoints like mydomain.com/username_or_handle/stuff. This creates conflict, since a user could have the same handle as an organisation.

Current solution:

I have a signal which, on pre-save of either a User or an Organisation, looks up BOTH the tables to ensure uniqueness, and raises a ValidationError if its violated.

@receiver(pre_save, sender=User)
@receiver(pre_save, sender=Organisation)
def check_valid_handle(sender, instance, **kwargs):
    """ The abbreviated gist...
    """
    if (Organisation.objects.filter(handle=instance.handle).count() > 0) or (User.objects.filter(username=instance.handle).count() > 0):
        raise ValidationError(detail='This handle is already taken, or prohibited. Please try another.'.format(type_str))

But strictly speaking, this could be subject to a race condition, as no lock is applied between the check time and the model creation / update time. Besides, it feels hacky.

Is it possible to apply this constraint at the DB level, without fundamentally altering my models to do multi table inheritance or polymorphism (which could be a nightmare to make work with AbstractUser without a lot of surgery)? And if so, how?

Answer 1

Years later, I've noticed this question had some activity so wanted to post a quasi-answer.

So the strict answer (as emerged in the comments) was "no" it's not straightforward to do this in your database schema.

The route I took

In my case, I made Organisation derive from django auth's Group model.

I also created a UserGroup which derived from Group and had a 1:1 relationship with User.

I then simply used the name field of the Group as the handle, with a unique constraint on there. Job done.

Another thing I'd think about

This wasn't available when I wrote the original question, but django now has a much more powerful constraints system that can be used to apply such constraints on create or update of the models.