Create DB Constraint via Django

  • A+
Category:Languages

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.


First issue: creating a database constraint through Django

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.

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 it all 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.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: