Create DB Constraint via Django

Question

I have a Django model which looks like this:

class Dummy(models.Model):
    ...
    system = models.CharField(max_length=16)

I want system never to be empty or to contain whitespace.

I know how to use validators in Django.

But I would enforce this at database level.

What is the easiest and django-like way to create a DB constraint for this?

I use PostgreSQL and don't need to support any other database.

Answer 1

First issue: creating a database constraint through Django

A) It seems that django does not have this ability build in yet. There is a 9-year-old open ticket for it, but I wouldn't hold my breath for something that has been going on this long.

Edit: As of release 2.2 (april 2019), Django supports database-level check constraints.

B) You could look into the package django-db-constraints, through which you can define constraints in the model Meta. I did not test this package, so I don't know how useful it really is.

# example using this package
class Meta:
    db_constraints = {
        'price_above_zero': 'check (price > 0)',
    }

---

Second issue: field system should never be empty nor contain whitespaces

Now we would need to build the check constraint in postgres syntax to accomplish that. I came up with these options:

1. Check if the length of system is different after removing whitespaces. Using ideas from this answer you could try:

   /* this check should only pass if `system` contains no
    * whitespaces (`\s` also detects new lines)
    */
   check ( length(system) = length(regexp_replace(system, '\s', '', 'g')) )
   

2. Check if the whitespace count is 0. For this you could us regexp_matches:

   /* this check should only pass if `system` contains no
    * whitespaces (`\s` also detects new lines)
    */
   check ( length(regexp_matches(system, '\s', 'g')) = 0 )
   

   Note that the length function can't be used with regexp_matches because the latter returns a set of text[] (set of arrays), but I could not find the proper function to count the elements of that set right now.

---

Finally, bringing both of the previous issues together, your approach could look like this:

class Dummy(models.Model):
    # this already sets NOT NULL to the field in the database
    system = models.CharField(max_length=16)

    class Meta:
        db_constraints = {
            'system_no_spaces': 'check ( length(system) > 0 AND length(system) = length(regexp_replace(system, "\s", "", "g")) )',
        }

This checks that the fields value:

1. does not contain NULL (CharField adds NOT NULL constraint by default)
2. is not empty (first part of the check: length(system) > 0)
3. has no whitespaces (second part of the check: same length after replacing whitespace)

Let me know how that works out for you, or if there are problems or drawbacks to this approach.