[Django] #32726: Enforce database level constraints for fields with limited choices
Django
-------------------------------------+-------------------------------------
Reporter: eeriksp | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: dev
layer (models, ORM) | Keywords: validation database
Severity: Normal | constraints
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
The following is based on PostgresSQL, but is applicable to other
databases as well.
When I define a `CharField`, for example this one from the official docs:
`year_in_school = models.CharField(max_length=2,
choices=YearInSchool.choices, default=YearInSchool.FRESHMAN)`
a regular `VARCHAR` field will be defined in the database with no
constraints preventing me from inserting other strings to it when I
interact directly with the database bypassing the Django layer.
This would compromise data integrity and lead to a state where an
unforeseen string is stored in that field which the application would not
be able to properly handle.
To prevent such a inconsistent state, I propose that one of the following
restrictions would be implemented (the choice might be database specific):
- add a `CHECK` constraint to the field which would ensure that the
value falls into the range of acceptable choices
- use the enum datatype to declare acceptable choices
- create an auxiliary table to store the acceptable choices and add a
foreign key constraint to the field
--
Ticket URL: <https://code.djangoproject.com/ticket/32726>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: validation database | Triage Stage:
constraints | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> The following is based on PostgresSQL, but is applicable to other
> databases as well.
>
> When I define a `CharField`, for example this one from the official docs:
>
> `year_in_school = models.CharField(max_length=2,
> choices=YearInSchool.choices, default=YearInSchool.FRESHMAN)`
>
> a regular `VARCHAR` field will be defined in the database with no
> constraints preventing me from inserting other strings to it when I
> interact directly with the database bypassing the Django layer.
>
> This would compromise data integrity and lead to a state where an
> unforeseen string is stored in that field which the application would not
> be able to properly handle.
>
> To prevent such a inconsistent state, I propose that one of the following
> restrictions would be implemented (the choice might be database
> specific):
> - add a `CHECK` constraint to the field which would ensure that the
> value falls into the range of acceptable choices
> - use the enum datatype to declare acceptable choices
> - create an auxiliary table to store the acceptable choices and add a
> foreign key constraint to the field
New description:
When I define a `CharField`, for example this one from the official docs:
`year_in_school = models.CharField(max_length=2,
choices=YearInSchool.choices, default=YearInSchool.FRESHMAN)`
a regular `VARCHAR` field will be defined in the database with no
constraints preventing me from inserting other strings to it when I
interact directly with the database bypassing the Django layer.
This would compromise data integrity and lead to a state where an
unforeseen string is stored in that field which the application would not
be able to properly handle.
To prevent such a inconsistent state, I propose that one of the following
restrictions would be implemented (the choice might be database specific):
- add a `CHECK` constraint to the field which would ensure that the
value falls into the range of acceptable choices
- use the enum datatype to declare acceptable choices
- create an auxiliary table to store the acceptable choices and add a
foreign key constraint to the field
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32726#comment:1>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: validation database | Triage Stage:
constraints | Unreviewed
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Eerik Sven Puudist (added)
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/32726#comment:2>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: validation database | Triage Stage:
constraints | Unreviewed
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Nick Pope):
I have a few comments:
> To prevent such a inconsistent state, I propose that one of the
following restrictions would be implemented (the choice might be database
specific):
If we were to implement something, we'd be unlikely to use different
approaches for different databases.
> - add a CHECK constraint to the field which would ensure that the value
falls into the range of acceptable choices
While this could theoretically be done, it does mean we'd be adding
migration operations for changes to `.choices` which previously generated
no operations. To avoid suddenly generating migrations for all existing
fields with `choices` we'd need to make this opt-in. I'm not sure if that
limits the usefulness somewhat.
It also add other complications - removal of a choice would cause an error
when applying the migration if the existing values were not changed in
advance. On large tables this can also trigger a revalidation of the
constraint which could be prohibitively expensive.
> - use the enum datatype to declare acceptable choices
Not all of the built-in database backends support `enum` types, e.g.
SQLite. There are also complications around management of changes to these
types if items need to be added or removed. It also requires new
operations in the database.
> - create an auxiliary table to store the acceptable choices and add a
foreign key constraint to the field
This is already possible manually by creating a `ForeignKey` manually.
----
Of all these options, I think the `enum` type isn't really viable. The
auxilliary table can already be done manually without adding unnecessary
complexity to Django to handle this. The `CHECK` constraint approach
perhaps could be considered, but would require careful thought. I'd
suggest taking this to the DevelopersMailingList.
--
Ticket URL: <https://code.djangoproject.com/ticket/32726#comment:3>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: dev
(models, ORM) |
Keywords: validation database | Triage Stage:
constraints | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
* needs_tests: 1 => 0
Comment:
I agree with Nick, I don't think that any of these options should be built
into Django.
> use the enum datatype to declare acceptable choices
This is already discussed in #24342.
> add a CHECK constraint to the field which would ensure that the value
falls into the range of acceptable choices
> create an auxiliary table to store the acceptable choices and add a
foreign key constraint to the field
Both options are already available in Django, however I don't think any of
them should be applied automatically, it's not worth extra complexity.
--
Ticket URL: <https://code.djangoproject.com/ticket/32726#comment:4>