Check Constraints for databases that support them

[Matt Schinckel]

I'm very interested in the idea of expanding the database level
constraints that can be supplied to a database schema, and also
automatically apply the same constraints to the model/view in django.

The various backends that support them, I believe already apply such
constraints for PositiveIntegerField() and so on.

I would like to see the ability to define extra constraints as part of
the model definition, and have these applied in a similar manner to
the unique and unique_together constraints.

I am more familiar with PostgreSQL than anything else, but I believe
the syntax I am about to propose will work in several databases.

Check constraints can be divided into two types: column level and
table level. With PostgreSQL, a column level constraint can reference
another column, but I would suggest that a constraint that does so
should be a table level constraint.

Column level constraints could be declared as a keyword argument to
the field constructor:

column = models.IntegerField(constraint='< 100')

Table level constraints could be declared as an attribute on the Meta
class of the model:

check_constraints = ('start < finish', )

Validation of the models should pick up when an incorrect constraint
has been declared. For instance, not using a valid operator ('# 100')
or not supplying a valid comparison value ('< 1e' for an IntegerField)
would be invalid column constraints, and comparing a Date to an
Integer would be an invalid table level constraint.

The code that should be generated would add "CHECK ('column' < 100)"
to the column definition in the first instance, and "CONSTRAINT
app_table_start_finish CHECK ('start' < 'finish')" to the table
definition in the second case.

I have already written code that fulfils these requirements, and some
tests for validation errors. I have also written additions to
BaseModelForm which validates according to the defined constraint(s).

At this stage, I have not allowed for more complicated relationships,
such as ('first < second + 65'), but this is planned.

Is there anyone else interested in this? Should I put this into a
ticket and attach my current diff?

[Tim Chase]

> Is there anyone else interested in this?

yes, I'd be interested in seeing some sort of database-level
CHECK constraint as part of Django. I had been sitting on my
thoughts until I see the GSoC work done on model-validation wend
its way towards trunk. My hope had been to see model validation
incorporate some DB-level CHECK constraints where feasible.

One of the other difficulties involves database expression
differences. For the simple cases such as you suggest, it's not
as bad as they're fairly standard. However, when functions are
involved, each DB seems to have its own family of functions.
E.g. if you want to assert the length of a string is 10
characters ("len" vs. "strlen"?) or the time is during business
hours ("hour(fieldname) between 8 and 17"...extracting
time-portions varies across DB engines).

I currently just add the CHECK constraints manually (well, in
post-syncdb code). Having them in a declarative fashion would
help keep them in the right place.

-tim

[Matt Schinckel]

On Sep 26, 10:27 pm, Tim Chase <django.us...@tim.thechases.com> wrote:
> > Is there anyone else interested in this?
>
> yes, I'd be interested in seeing some sort of database-level
> CHECK constraint as part of Django.  I had been sitting on my
> thoughts until I see the GSoC work done on model-validation wend
> its way towards trunk.  My hope had been to see model validation
> incorporate some DB-level CHECK constraints where feasible.

I did have a brief look at the django-check-constraint project on
google code, but it was overkill for what I needed.

> One of the other difficulties involves database expression
> differences.  For the simple cases such as you suggest, it's not
> as bad as they're fairly standard.  However, when functions are
> involved, each DB seems to have its own family of functions.
> E.g. if you want to assert the length of a string is 10
> characters ("len" vs. "strlen"?) or the time is during business
> hours ("hour(fieldname) between 8 and 17"...extracting
> time-portions varies across DB engines).

Yeah, for the time being I was only interested in simple cases.
Comparing against a static value in the case of column constraints,
and comparing two columns in the table-level case. I don't even allow
for 'start < finish + 1' in the code I have written so far.

> I currently just add the CHECK constraints manually (well, in
> post-syncdb code).  Having them in a declarative fashion would
> help keep them in the right place.

Not to mention that it will allow the same code to control the db-
level constraint, and the django validation (admin, form). Which is
DRY, and one of the reasons I am moving projects to django.

I also looked a little bit at south, which I have just started using
for migrations. It doesn't have anything in there that is that
useful, other than raw SQL. Which can do those type of things, but it
is nicer to move into the declaration.

Matt.