[Django] #34357: UniqueConstraint not working with Sqlite3

[Django]

#34357: UniqueConstraint not working with Sqlite3
-----------------------------------------+------------------------
Reporter: Conrad | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.1
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
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?

--
Ticket URL: <https://code.djangoproject.com/ticket/34357>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#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:

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

--

--
Ticket URL: <https://code.djangoproject.com/ticket/34357#comment:1>