Opened 21 months ago

Last modified 21 months ago

#34357 closed Bug

UniqueConstraint not working with Sqlite3 — at Version 1

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 (1)

comment:1 by Conrad, 21 months ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top