UniqueConstraint doesn't violate NULL values on SQLite.

[Conrad]

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?

[Mariusz Felisiak]

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 multiple NULL-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.

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.

This is the right way to add unique partial constraint on SQLite.

[Conrad]

Thanks for the quick response! Do you have any advise on how to proceed with this issue for me?