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

2019 Update

Django 2.2 added support for database-level constrains. The new CheckConstraint and UniqueConstraint classes enable adding custom database constraints. Constraints are added to models using the Meta.constraints option.

Your system validation would look like something like this:

from django.db import models
from django.db.models.constraints import CheckConstraint
from django.db.models.query_utils import Q


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

    class Meta:
        constraints = [
            CheckConstraint(
                check=~Q(system="") & ~Q(system__contains=" "),
                name="system_not_blank")
        ]

Answer 2

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.