CODEWITHSUNDEEP
sqldjango
Sam Bobel
Sam Bobel

Reputation: 1824

How to create a unique-together constraint in Django that ignores NULL?

How does Django deal with NULL with its unique index? Is there a way to allow for many NULL rows, while enforcing uniqueness on non-null values?

And then, the same question for unique_together. Is there a way to enforce unique_together, but if any of the fields are NULL, allow for that row?

Upvotes: 2

Views: 1326

Answers (1)

Daniel Hepper
Daniel Hepper

Reputation: 29967

I'm not sure about MySQL, but SQLite and PostgreSQL do not consider NULL values to be equal, so the behavior you are looking for is actually the default.

You can verify this yourself:

class MyModel(models.Model):
    title = models.CharField(unique=True, null=True, max_length=255)

>>> MyModel.objects.create()
<MyModel: MyModel object (1)>
>>> MyModel.objects.create()
<MyModel: MyModel object (2)>

For a unique_together:

class MyModel(models.Model):
    title = models.CharField(null=True, max_length=255)
    description = models.CharField(null=True, max_length=255)

    class Meta:
        unique_together = (('title', 'description'),)

>>> MyModel.objects.create(title='x')
<MyModel: MyModel object (1)>
>>> MyModel.objects.create(title='x')
<MyModel: MyModel object (2)>

Note that in the second example, there is no unique constraint on the title field, only the unique together on both fields.

Upvotes: 4

Related Questions