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.

Bowne

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.

Keverne

Yes, your solution with the app django-db-constraints works. This is much better than writing migrations by hand with migrations.RunSQL(...). – Bowne

That ticket is now fixed and the changes will be available, most likely, in Django 2.2. – Rapier

Confirmed availability in 2.2: docs.djangoproject.com/en/dev/ref/models/constraints – Uraemia

Answer 2

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")
        ]

Amparoampelopsis

This answer should be THE solution since it uses builtin' Django feature to achieve the desired goal. – Sorenson

Answer 3

You can add CHECK constraint via custom django migration. To check string length you can use char_length function and position to check for containing whitespaces.

Quote from postgres docs (https://www.postgresql.org/docs/current/static/ddl-constraints.html):

A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.

To run arbitrary sql in migaration RunSQL operation can be used (https://docs.djangoproject.com/en/2.0/ref/migration-operations/#runsql):

Allows running of arbitrary SQL on the database - useful for more advanced features of database backends that Django doesn’t support directly, like partial indexes.

Create empty migration:

python manage.py makemigrations --empty yourappname

Add sql to create constraint:

# Generated by Django A.B on YYYY-MM-DD HH:MM
from django.db import migrations

class Migration(migrations.Migration):

    dependencies = [
        ('yourappname', '0001_initial'),
    ]

    operations = [
         migrations.RunSQL('ALTER TABLE appname_dummy ADD CONSTRAINT syslen '
                           'CHECK (char_length(trim(system)) > 1);',
                           'ALTER TABLE appname_dummy DROP CONSTRAINT syslen;'),
         migrations.RunSQL('ALTER TABLE appname_dummy ADD CONSTRAINT syswh '
                           'CHECK (position(' ' in trim(system)) = 0);',
                           'ALTER TABLE appname_dummy DROP CONSTRAINT syswh;')


    ]

Run migration:

python manage.py migrate yourappname

Prakrit

Answer 4

I modify my answer to reach out your requirements.

So, if you would like to run a DB constraint try this one :

import psycopg2
def your_validator():
    conn = psycopg2.connect("dbname=YOURDB user=YOURUSER")
    cursor = conn.cursor()
    query_result = cursor.execute("YOUR QUERY")
    if query_result is Null:
        # Do stuff
    else:
        # Other Stuff

Then use the pre_save signal.

In your models.py file add,

from django.db.models.signals import pre_save
class Dummy(models.Model):
...
    @staticmethod
    def pre_save(sender, instance, *args, **kwargs)
        # Of course, feel free to parse args in your def.
        your_validator()

Yorktown

Thank you for trying to help. Your solution does validation inside Django. I want validation inside the database. – Bowne

You re welcome @guettli. My apologies i didn't read I know how to use validators in Django. – Yorktown

@Bowne i just modify my answer – Yorktown

why create a DB connection? My code uses Django and I would like to use the django way to get to a DB connection/cursor. I don't see that you create a db constraint. You execute SQL in the validation routine. For me this is something different. – Bowne