Enforce database level constraints for fields with limited choices

[Eerik Sven Puudist]

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

[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.

[Mariusz Felisiak]

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.