[FIXED] sqlalchemy, using check constraints

Issue

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)

Solution

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.

Answered By - MOCKBA