Added support for functional constraints.

[Safwan Rahman]

Case insensitive unique constraint is needed for various purposes like storing emails. In postgresql, its possible to add ​a functional unique index with `Lower` and it will work as a case insensitive unique constraint. I have tried to implement that functional unique index/constraint in django, but looking at the source it seems that it is not possible to add functional unique index/constraint in django. ​PR 11929 is created for adding functional index, but it seems like it is not possible to create unique functional index with it.

I think there should be a option like case_insensitive=True that can be passed to UniqueConstraint so the UniqueConstraint create a functional unique index with Lower.

I know, its possible to store the value as lower case always and mitigate this issue, but it does not provide DB level constrains safety from anomaly.

[Simon Charette]

Should we close this one as a duplicate of #26167.

I'll at least rename the issue to more generic name.

[Mariusz Felisiak]

Probably some or most of logic can be reused from functional indexes (#26167) (e.g. with a mixin) but I think we should treat this as a separate feature.

[Carlos_Mir_de_Souza]

Looking the history in github it seems that was closed because the code was merged, but after that it had improvement/modifications, it would be interesting if the author can clarify the status of the PR, if the discussion is about the same thing or about other things.

[Mariusz Felisiak]

CarlosMirdeSouza, this ticket doesn't have a patch.

[Carlos_Mir_de_Souza]

He references the PR 11929, it doesn't count has a patch?
​https://github.com/django/django/pull/11929

[Carlos_Mir_de_Souza]

I was confused, my bad, sorry for the mistake, it references the PR for other ticket.

[Mariusz Felisiak]

​PR11929 is about functional indexes, not functional constraints.

[Carlos_Mir_de_Souza]

Thanks for the explanation, i will have more attention for next time.

[saadalsaad]

from my understanding, we need to support for functional constraints by adding case_insensitive option to UniqueConstraint ?
​https://github.com/django/django/pull/12115

[Mariusz Felisiak]

saadalsaad, no. This ticket is about adding support for functional constraints not only about case insensitive check constraints. It's more generic.

[saadalsaad]

felixxm, since this ticket is generic for functional constraints, can we move case_insensitive option to a sperate ticket?

[Mariusz Felisiak]

saadalsaad, not really we don't want to add such specific parameters.

[saadalsaad]

Replying to Safwan Rahman:

Case insensitive unique constraint is needed for various purposes like storing emails. In postgresql, its possible to add ​a functional unique index with `Lower` and it will work as a case insensitive unique constraint. I have tried to implement that functional unique index/constraint in django, but looking at the source it seems that it is not possible to add functional unique index/constraint in django. ​PR 11929 is created for adding functional index, but it seems like it is not possible to create unique functional index with it.

I think there should be a option like case_insensitive=True that can be passed to UniqueConstraint so the UniqueConstraint create a functional unique index with Lower.

I know, its possible to store the value as lower case always and mitigate this issue, but it does not provide DB level constrains safety from anomaly.

Ticket description suggested adding case_insensitive option to be added in UniqueConstraint to create a functional unique index with Lower
since we are not going to add such option, seems we need to modify ticket description
felixxm, do you mean supporting functional constraints (supporting expressions in class based constraints ?) similar to indexes in #26167 ?

Thanks

[Mariusz Felisiak]

Replying to saadalsaad:

felixxm, do you mean supporting functional constraints (supporting expressions in class based constraints ?) similar to indexes in #26167 ?

Thanks

Yes exactly, see comment.

[Sanskar Jaiswal]

Was a common consensus reached regarding this? I would love to give this a shot if I could understand what all we expect from this feature.

[Hannes Ljungberg]

I intend to create a patch for this after #26167 is merged.

[Hannes Ljungberg]

PR: ​https://github.com/django/django/pull/13983

[Mariusz Felisiak <felisiak.mariusz@…>]

In 3aa5452:

Fixed #30916 -- Added support for functional unique constraints.

Thanks Ian Foote and Mariusz Felisiak for reviews.