[Fixed]-Create DB Constraint via Django

by

14👍

✅

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.

👤Ralf

29👍

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")
        ]
👤Cesar Canassa

9👍

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
👤ndpu

2👍

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()
👤Panos Angelopoulos

Leave a comment