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 )
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?