Opened 21 months ago

Closed 21 months ago

Last modified 21 months ago

#34357 closed Bug (invalid)

UniqueConstraint doesn't violate NULL values on SQLite.

Reported by: Conrad Owned by: nobody
Component: Database layer (models, ORM) Version: 4.1
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Conrad)

I have the following constraint on my model, which should achieve that there are never 2 "not-ended" instances to each contract (https://stackoverflow.com/questions/75436822/django-uniqueconstraint-not-working-as-expected as reference):

        constraints = [
            models.UniqueConstraint(fields=['contract', 'ended'], condition=models.Q(
                ended__isnull=True), name='unique_contract_ended'),
        ]

Automatically created migration looks like this:

    operations = [
        migrations.AddConstraint(
            model_name='contractinstance',
            constraint=models.UniqueConstraint(condition=models.Q(('ended__isnull', True)), fields=('contract', 'ended'), name='unique_contract_ended'),
        ),
    ]

however it is not properly working in my sqlite3 database after migrating. The following testcase runs through without a hitch:

        with self.assertRaises(IntegrityError):
            ContractInstance.objects.create(
                contract=contract, ended=None)
            ContractInstance.objects.create(
                contract=contract, ended=None)

I don't really understand the issue, I chose the above UniqueConstraint after experimenting with unique_together for the two fields, which didn't work either. I know that I can use the validation/ clean methods to ensure this constraint, however due to unfortunate caching it has happend in the past, that there were constraint violations in the past. In an Sqlite viewer I can see that the constraint was added (CREATE UNIQUE INDEX "unique_contract_ended" ON "plugin_contractinstance" ("contract_id", "ended") WHERE "ended" IS NULL), but if I understand https://stackoverflow.com/questions/15497985/how-to-add-unique-constraint-to-existing-table-in-sqlite correctly it is not really the right way of adding the constraint. Maybe there should be a warning or another way of adding the constraint?

Change History (3)

comment:1 by Conrad, 21 months ago

Description: modified (diff)

comment:2 by Mariusz Felisiak, 21 months ago

Component: UncategorizedDatabase layer (models, ORM)
Resolution: invalid
Status: newclosed
Summary: UniqueConstraint not working with Sqlite3UniqueConstraint doesn't violate NULL values on SQLite.

Thanks for the report. NULL doesn't violate unique constraints or cause conflicts in most (all?) databases, that's why it's possible to add multiple NULL-values. This is a database behavior, not something that Django is responsible for. I'm not sure Django documentation is the right place to document this. Closing as "invalid" unless someone strongly believes we should document this.

but if I understand ​https://stackoverflow.com/questions/15497985/how-to-add-unique-constraint-to-existing-table-in-sqlite correctly it is not really the right way of adding the constraint.

This is the right way to add unique partial constraint on SQLite.

comment:3 by Conrad, 21 months ago

Thanks for the quick response! Do you have any advise on how to proceed with this issue for me?

Note: See TracTickets for help on using tickets.
Back to Top