#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 )
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 , 21 months ago
Description: | modified (diff) |
---|
comment:2 by , 21 months ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Resolution: | → invalid |
Status: | new → closed |
Summary: | UniqueConstraint not working with Sqlite3 → UniqueConstraint doesn't violate NULL values on SQLite. |
comment:3 by , 21 months ago
Thanks for the quick response! Do you have any advise on how to proceed with this issue for me?
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 multipleNULL
-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.This is the right way to add unique partial constraint on SQLite.