How can I prevent certain combinations of values in a table, derived from the values already in the table?

Question

I'm developing a web app for storing and managing tasks, using Flask-SQLAlchemy to talk to the backend. I want to use a table to store priority comparisons between pairs of tasks in order to construct a partial ordering. The table will have two columns, one for the lesser element and one for the greater element, and both columns together will form the primary key.

My code so far looks like this:

class PriorityPair(db.Model):
    lesser_id = db.Column(db.Integer, db.ForeignKey('task.id'),
                          primary_key=True)
    lesser = db.relationship('Task', remote_side=[id])
    greater_id = db.Column(db.Integer, db.ForeignKey('task.id'),
                           primary_key=True)
    greater = db.relationship('Task', remote_side=[id])

    def __init__(self, lesser, greater):
        self.lesser = lesser
        self.greater = greater

All told, this should be sufficient for what I want to do with the table, but there's a problem in that inconsistent rows might be inserted. Suppose I have two tasks, A and B. If task A is of greater priority than task B, I could do the following:

pair = PriorityPair(task_b, task_a)
db.session.add(pair)
db.session.commit

and the relation between the two would be stored as desired. But if at some future point, the opposite relation, PriorityPair(task_a, task_b), were to be inserted into the table, that would be inconsistent. The two tasks can't be greater in importance than each other at the same time.

Now I could probably prevent this in python code, but I'd like to be sure that the DB table itself is guaranteed to remain consistent. Is it possible to put (via Flask-SqlAlchemy) some kind of constraint on the table, so that if (A,B) is already present, then (B,A) will be automatically rejected? And would such a constraint work across DB backends?

You could probably create a [CHECK constraint](http://docs.sqlalchemy.org/en/latest/core/constraints.html#check-constraint) that enforces the transitive property of the ordering, but cross-DB portability is limited. — Lukas Graf, Apr 29 '16 at 20:02
I haven't used CHECK constraints myself yet for anything non-trivial, and I currently lack the time and necessary setup to write a half-decent answer. But if you can figure out how to make it work for your use case, I'll be happy to upvote if you post it as a self-answer. — Lukas Graf, May 02 '16 at 14:55
@LukasGraf Sure, I can do that, but I was expecting to accept your answer, so that you would get the points. — izrik, May 02 '16 at 14:57

score 0 · Accepted Answer · edited May 23 '17 at 11:52

No and No.

This is not possible. SqlAlchemy has support for CHECK constraints, but the expression of the check is given as a string. It will require a sub-query, something like (greater_id, lesser_id) not in (select sub.lesser_id, sub.greater_id from priority_pair as sub). And the underlying database backends will prevent it:

MySQL ignores all CHECK constraints.
SQLite does not allow sub-queries in CHECK constraints.
PostgreSQL does not allow sub-queries in CHECK constraints.
Oracle does not allow sub-queries in CHECK constraints.

Instead, you must find some other solution, whether it's triggers or just changing the whole model, which is what I decided to do.

How can I prevent certain combinations of values in a table, derived from the values already in the table?

1 Answers1