Avoiding Duplicate Entries When One or More Fields Are None

Using Django Using the ORM
1

Hi everyone,

I’m working on a Django model for storing addresses, and I need to enforce uniqueness across five fields: street, house_number, city, state, and zip_code. The challenge is that one or more of these fields can be None, and I want to avoid duplicates even in such cases.

Here’s my model:

class Address(models.Model):
    street = models.CharField(max_length=255, null=True, blank=True)
    house_number = models.CharField(max_length=50, null=True, blank=True)
    city = models.CharField(max_length=100, null=True, blank=True)
    state = models.CharField(max_length=2, null=True, blank=True)
    zip_code = models.CharField(max_length=10, null=True, blank=True)
    
    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=['street', 'house_number', 'city', 'state', 'zip_code'],
                name='unique_address_with_nulls',
            )
        ]

What I’m Trying to Achieve:

  • Prevent duplicate entries where all non-None fields match.
  • For example, these should be considered duplicates:
    1. street="123 Elm", house_number=None, city="Springfield", state="CA", zip_code="12345"
    2. street="123 Elm", house_number=None, city="Springfield", state="CA", zip_code="12345"

What is the best and most efficient way to achieve this?

Thank you!

2

If you’re using Postgres I’d suggest having a look at nulls_distinct otherwise you’ll likely have to coalese your nullable fields to a sentinel value to force collisions of nulls.

3

Also, you could use a functional unique constraint: Constraints reference | Django documentation | Django

class Address(models.Model):
    street = models.CharField(max_length=255, null=True, blank=True)
    house_number = models.CharField(max_length=50, null=True, blank=True)
    city = models.CharField(max_length=100, null=True, blank=True)
    state = models.CharField(max_length=2, null=True, blank=True)
    zip_code = models.CharField(max_length=10, null=True, blank=True)
    
    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=['street', 'house_number', 'city', 'state', 'zip_code'],
                name='unique_address_with_nulls',
                condition=models.Q(),  # Ensure that the constraint applies to all rows.
                deferrable=models.Deferrable.DEFERRED,  # Optional: it helps with bulk operations in case you need it
                expressions=[
                    models.functions.Coalesce('street', models.Value('')),
                    models.functions.Coalesce('house_number', models.Value('')),
                    models.functions.Coalesce('city', models.Value('')),
                    models.functions.Coalesce('state', models.Value('')),
                    models.functions.Coalesce('zip_code', models.Value(''))
                ]
            )
        ]