Create indexes with expression using UniqueConstraint

[Wladimir Guerra]

Hello,

First of all, you've being doing a great work here. Django is an amazing framework!

I have been woking in a new project and I need to do a multiple columns unique validation in which some of them is nullable. So I've use a UniqueConstraint like:

class Item(Model):

    name = models.CharField(max_length=200, null=False)

    unit = models.CharField(max_length=20, null=True)

    owner = models.CharField(max_length=200, null=True)

    class Meta:

        constraints = [

          models.UniqueConstraint(

              name='%(app_label)s_%(class)s_name_unit_owner_uniq',

              fields=('name', 'unit', 'owner')

          ),

        ]

Unfortunately I wasn't achieving what I wanted. In that simple example a user could create duplicated records for unit = None. Also, It is possible to create duplicated records where .This behavior conforms the SQL standards, of course. 

In this article I explain how I have done to get the wanted behavior and the reasons that it might be cumbersome in some cases. In the same article I show an SQL expression that is clean and should result in a way more DRY code in model declaration. The SQL expression is:

CREATE UNIQUE INDEX core_item_name_unit_owner_uniq
     ON public.core_item (name, COALESCE(owner,"-"), COALESCE(unit,"-"));

[Hannes Ljungberg]

Hi!

Support for using expressions on UniqueConstraints will be available in Django 4.0: https://docs.djangoproject.com/en/dev/releases/4.0/#functional-unique-constraints

[Wladimir Guerra]

Hi

Thanks for the reply.

I’ve accidentally pushed the send button before finish the message. 🤦🏻‍♂️

Regards,

Wladimir

-- 
You received this message because you are subscribed to a topic in the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-developers/zqszCYWzxDI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-develop...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/388b0350-bfd7-4e5f-a3c9-f963f2ab2eb6n%40googlegroups.com.