Features  

Introduction

To implement check constraints on models.

The various check constraints to be implemented:

1. Range Based Check Constraints
2. Value Based Check Constraints

Most Database engines like Postgresql, Sqlite, Firebird etc have built in support for check constraints. So for these database engines the check constraints can be implemented at the database level whereas for MySQL (which does not support check constraints),it has to be implemented at the application level (ie in Django)(currently not to be implemented for MySQL).

Example 1: Range based Constraint

from check_constraints import Check

class Product(models.Model):
    product_name = models.CharField(maxlength=50)
    discount = models.IntegerField()
    price = models.IntegerField()
    tax_percent = models.IntegerField()

    class Meta:
      constraints = (
                     ("check_discount",Check(discount__gte = 0,discount__lte = 100)),
                     ("check_price",Check(price__gt=0) & Check(discount__lt='price')),
                     ("check_tax_percent",Check(tax_percent__gte = 10) | Check(tax_percent__lte = 15)),
                     ("check_product_name",Check(product_name__in = ("Soap","Shampoo","Detergent"))),
                    )

Discount and price can only be a non-negative value. Also it checks for discount < 100 and if price > discount and price is between 0 and 10,000.

The various Range based constraints being

 * greater than               gt           >
 * lesser than                lt           <
 * equal to                   eq           =
 * not equal to               neq          <>
 * lesser than or equal to    lte          <=
 * greater than or equal to   gte          >=
 * between                    between      (expects a range)

Range based constraints can be used to check for constraints for more than one column (as seen in the example above).

Example 2: Value Based Constraints

from check_constraints import Check
class Person(models.Model):
    first_name = models.CharField(maxlength=50)
    last_name = models.CharField(maxlength=50)
    gender = models.CharField()

    class Meta:
      constraints = (
                     ("check_name" , Check(last_name__neq = 'first_name')),
                     ("check_gender" , Check(gender__in__upper = ('MALE','FEMALE'))),
                    )

The Value based constraints being

• IN, NOT_IN to check whether the input is in a given list
• Note: (For string fields only)Use 'IN' when checking for given strings, for e.g. first_name__in = ('John',). Use 'eq' and 'neq' when comparing string field with another string field like first_name__neq = 'last_name'.
• LIKE, UNLIKE to check if the operand resembles a value (regexps are possible)
• "*" matches 0 or more characters.
• "." matches 1 or more character.

Example:

from check_constraints import Check

class Manufacturer(models.Model):
     name = models.CharField(maxlength=50)

     class Meta:
       constraints = (
                       ("check_name",Check(name__like='Merced*')),
                     )

• NULL to check if the given value is a Null or not (already implemented)
• UNIQUE to check for uniqueness of column in the table (already implemented)

Example 3:

from check_constraints import Check
from datetime import date

class Project(models.Model):
    proj_name = models.CharField(maxlength=50)
    start_date = models.DateField()
    end_date = models.DateField()

    class Meta:
      constraints = (
                     ("check_name" , Check(proj_name__like='WorldOnline*')),
                     ("check_start_date" , Check(start_date__gte = date(2007,01,01)),
                     ("check_date" , Check(start_date__lte = 'end_date')),
                    )

The above model checks if project name contains WorldOnline at the beginning of the proj_name, the project start date is after January 1st 2007 and project end date is after the start date. Simple database based validations done with three small lines in Python (also these lines are similar to the field set lookups, so not much of learning required).

When are these check constraints invoked?

These check constraints are called whenever a model object is created or edited.

Benefits of Check Constraints:

• Allows the programmer to write constraints in Python (without having to know SQL)(Sticking to Django's philosophy, purists will not be disappointed.)
• Check constraints are used to ensure the validity of data in a database and to provide data integrity. If they are used at the database level, applications that use the database will not be able to add invalid data or modify valid data so the data becomes invalid, even if the application itself accepts invalid data.
• Check constraints provide better data integrity. As check constraints are always executed whenever the data in the column upon which they are defined is to be modified.
• Check constraints promote consistency. Because they are implemented once, in the table DDL, each constraint is always enforced. Constraints written in application logic, on the other hand, must be executed by each program that modifies the data to which the constraint applies. This can cause code duplication and inconsistent maintenance resulting in inaccurate business rule support.