Opened 3 years ago
Closed 3 years ago
#32726 closed New feature (wontfix)
Enforce database level constraints for fields with limited choices
Reported by: | Eerik Sven Puudist | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | validation database constraints |
Cc: | Eerik Sven Puudist | Triage Stage: | Unreviewed |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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
Change History (4)
comment:1 by , 3 years ago
Description: | modified (diff) |
---|
comment:2 by , 3 years ago
Cc: | added |
---|---|
Needs tests: | set |
comment:3 by , 3 years ago
comment:4 by , 3 years ago
Needs tests: | unset |
---|---|
Resolution: | → wontfix |
Status: | new → closed |
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.
I have a few comments:
If we were to implement something, we'd be unlikely to use different approaches for different databases.
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 withchoices
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.
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.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. TheCHECK
constraint approach perhaps could be considered, but would require careful thought. I'd suggest taking this to the DevelopersMailingList.