Opened 10 months ago

Last modified 4 months ago

#30916 assigned New feature

Added support for functional constraints.

Reported by: Safwan Rahman Owned by: saadalsaad
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: constraint functional
Cc: Ian Foote Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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.

Change History (19)

comment:1 Changed 10 months ago by Safwan Rahman

Description: modified (diff)

comment:2 Changed 10 months ago by Safwan Rahman

Description: modified (diff)

comment:3 Changed 10 months ago by Simon Charette

Keywords: constraint functional added; UniqueConstraint removed
Summary: Add support for case insensitive unique constraintAdded support for functional constraints
Triage Stage: UnreviewedAccepted

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

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

comment:4 Changed 10 months ago by saadalsaad

Owner: changed from nobody to saadalsaad
Status: newassigned

comment:5 Changed 10 months ago by felixxm

Summary: Added support for functional constraintsAdded support for functional constraints.
Version: 2.2master

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.

comment:6 Changed 9 months ago by Carlos_Mir_de_Souza

Has patch: set

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 topic or about other things.

Last edited 9 months ago by Carlos_Mir_de_Souza (previous) (diff)

comment:7 Changed 9 months ago by felixxm

Has patch: unset

CarlosMirdeSouza, this ticket doesn't have a patch.

Last edited 9 months ago by felixxm (previous) (diff)

comment:8 Changed 9 months ago by Carlos_Mir_de_Souza

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

comment:9 Changed 9 months ago by Carlos_Mir_de_Souza

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

Last edited 9 months ago by Carlos_Mir_de_Souza (previous) (diff)

comment:10 Changed 9 months ago by felixxm

PR11929 is about functional indexes, not functional constraints.

comment:11 Changed 9 months ago by Carlos_Mir_de_Souza

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

comment:12 Changed 9 months ago by 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

comment:13 Changed 9 months ago by felixxm

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

comment:14 Changed 9 months ago by saadalsaad

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

comment:15 Changed 9 months ago by felixxm

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

comment:16 Changed 9 months ago by 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

comment:17 in reply to:  16 Changed 9 months ago by felixxm

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.

comment:18 Changed 5 months ago by 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.

comment:19 Changed 4 months ago by Ian Foote

Cc: Ian Foote added
Note: See TracTickets for help on using tickets.
Back to Top