id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34357	UniqueConstraint doesn't violate NULL values on SQLite.	Conrad	nobody	"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?"	Bug	closed	Database layer (models, ORM)	4.1	Normal	invalid			Unreviewed	0	0	0	0	0	0