#34357: UniqueConstraint not working with Sqlite3
-------------------------------+--------------------------------------
Reporter: Conrad | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------------------------
Description changed by Conrad:
Old description:
> I have the following constraint on my model:
> {{{
> 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?
New description:
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?
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34357#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018671537bbd-3a72ecaf-8988-4150-9c6d-9cc0262af44c-000000%40eu-central-1.amazonses.com.
