sqlalchemy, using check constraints

Question

I just started on sqlalchemy and I want to put a check constraint on one of my columns. I have a column called startTime and endTime and I want to ensure that endTime > startTime.

from sqlalchemy import Column, Integer, String, ForeignKey, Date
import models.Base

class Session(Base):
    __tablename__ = 'sessions'

    sid = Column(Integer, primary_key=True)
    uid = Column(Integer, ForeignKey('users.uid'), nullable=False)
    startTime= Column(Date, nullable=False)

    #probably won't work
    endTime = Column(Date, CheckConstraint('endTime > startTime'), nullable=False)

Answer 1

Apart from the fact that MySQL doesn't support check constraints, I think the issue is that you are trying to refer to multiple columns in a column level check constraint.

Assuming that you used another database, you need to define the constraint at the table level, something like this:

from sqlalchemy import Column, Integer, String, ForeignKey, Date
import models.Base

class Session(Base):
    __tablename__ = 'sessions'
    __table_args__ = (
        CheckConstraint('endTime > startTime'),
    )

    sid = Column(Integer, primary_key=True)
    uid = Column(Integer, ForeignKey('users.uid'), nullable=False)
    startTime= Column(Date, nullable=False)

    endTime = Column(Date, nullable=False)

Answer 2

It's a valid syntax, but in MySQL (I assume you're using MySQL?) this will be ignored. From SQLAlchemy docs:

Check constraints can be named or unnamed and can be created at the Column or Table level, using the CheckConstraint construct. The text of the check constraint is passed directly through to the database, so there is limited “database independent” behavior. Column level check constraints generally should only refer to the column to which they are placed, while table level constraints can refer to any columns in the table. Note that some databases do not actively support check constraints such as MySQL.*

You can create a trigger of course, but then you'd put your biz logic to the DB layer. I'd write an app-level constructor check instead.