# Bulk\_create with UniqueConstraint

**URL:** <https://forum.djangoproject.com/t/bulk-create-with-uniqueconstraint/30508>\
**Category:** Using the ORM\
**Created:** [April 24, 2024, 1:19pm UTC](https://forum.djangoproject.com/t/bulk-create-with-uniqueconstraint/30508 "2024-04-24T13:19:13Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![valeriuv](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/valeriuv/32/15025_2.png) [@valeriuv](https://forum.djangoproject.com/u/valeriuv)\
**Post date:** [April 24, 2024, 1:19pm UTC](https://forum.djangoproject.com/t/bulk-create-with-uniqueconstraint/30508/1 "2024-04-24T13:19:13Z")

</div>

When trying to bulk\_create with update\_conflicts=True and a UniqueConstraint on the model, I get duplicate entries.

Here’s the model:

```auto
class TripSamsara(models.Model):
    driver_1 = models.ForeignKey(Driver, on_delete=models.CASCADE, related_name='driver_1_trips_samsara', null=True, blank=True)
    driver_2 = models.ForeignKey(Driver, on_delete=models.CASCADE, related_name='driver_2_trips_samsara', null=True, blank=True)
    truck = models.ForeignKey(Truck, on_delete=models.CASCADE, null=True)
    trailer = models.ForeignKey(Trailer, on_delete=models.CASCADE, null=True)
    start_location = models.CharField(max_length=255, null=True, blank=True)
    end_location = models.CharField(max_length=255, null=True, blank=True)
    start_date = models.DateTimeField(null=True, blank=True)
    end_date = models.DateTimeField(null=True, blank=True)
    distance_meters = models.IntegerField(null=True, blank=True)
    fuel_consumed_ml = models.IntegerField(null=True, blank=True)
    start_odometer = models.IntegerField(null=True, blank=True)
    end_odometer = models.IntegerField(null=True, blank=True)
    
    class Meta:
        verbose_name_plural = 'Trips Samsara'
        constraints = [
            models.UniqueConstraint(fields=['driver_1', 'driver_2', 'truck', 'start_odometer', 'end_odometer'], name='unique_trip_samsara')
        ]

```

Here’s the function:

```auto
samsara_created_trips = TripSamsara.objects.bulk_create(
                            trips_bulk_create,
                            update_conflicts=True,
                            unique_fields=['driver_1', 'driver_2', 'truck', 'start_odometer', 'end_odometer'],
                            update_fields=['trailer', 'start_location', 'end_location', 'distance_meters', 'fuel_consumed_ml', 'start_date', 'end_date']
                        )

```

The db table seems to have the unique constraint as well:

 ![Screenshot 2024-04-24 at 16.18.05](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/3X/8/4/84703e8b56f1b476ae4ddd00f64ec3cd43ab016d.png)

What am I missing?

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [April 24, 2024, 2:37pm UTC](https://forum.djangoproject.com/t/bulk-create-with-uniqueconstraint/30508/2 "2024-04-24T14:37:40Z")

</div>

Do any of the fields contain `NULL` values? I see a few of the fields included in your unique constraints are nullable (`null=True`).

If any member of a composite unique constraint is `NULL` then any tuple containing them will be considered distinct because `NULL != NULL` in SQL.

In other words when taking null values into consideration your unique constraint definition is equivalent to

```auto
UniqueConstraint(
    fields=['driver_1', 'driver_2', 'truck', 'start_odometer', 'end_odometer'],
    condition=Q(
        driver_1__isnull=False,
        driver_2__isnull=False,
        truck__isnull=False,
        start_odometer__isnull=False,
        end_odometer__isnull=False,
    )
    name='unique_trip_samsara',
)

```

With the exception that the lack of explicit `condition`, as you’ve defined it, stores the null-containing tuples in the index b-tree and thus the latter can be used for their efficient retrieval.

A way around that that I know of is using the `NULLS NOT DISTINCT` [option of Postgres 15+](https://www.postgresql.org/docs/current/sql-createindex.html) [which Django supports since 5.0](https://docs.djangoproject.com/en/5.0/ref/models/constraints/#nulls-distinct) or to use an expression index that `COALESCE` null values into sentinel to denote emptyness.

For example

```auto
UniqueConstraint(
    expression=Func(
        Coalesce("driver_1", 0),
        ...,
        Coalesce("end_odometer", 0),
        function="ROW"
    )
    name='unique_trip_samsara',
)

```

---

<div class="post-metadata">

**Author:** ![valeriuv](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/valeriuv/32/15025_2.png) [@valeriuv](https://forum.djangoproject.com/u/valeriuv)\
**Post date:** [April 24, 2024, 5:19pm UTC](https://forum.djangoproject.com/t/bulk-create-with-uniqueconstraint/30508/3 "2024-04-24T17:19:11Z")

</div>

Thank you, Simon!

I think you’re right – most of the entries in the database have the field ‘driver\_2’ null. I’m currently on Django 4.2 and would like to avoid an upgrade, at least at this point in time.

I’ll check to see if removing the ‘driver\_2’ from the unique constraint will still make entries unique. Otherwise, I’m looking at generating a UUID5 for each combination and using that as an ID.