Enforce database level constraints for fields with limited choices

[Eerik Sven Puudist]

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