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)

Have you tried this? What error do you get?

univerio
– univerio

2016-02-25 02:50:14 +00:00
Commented Feb 25, 2016 at 2:50 — univerio
– univerio, Commented Feb 25, 2016 at 2:50

Community · Accepted Answer · 2022-10-14 14:16:32Z

24

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, CheckConstraint
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)

edited Oct 14, 2022 at 14:16

CommunityBot

11 silver badge

answered Jul 8, 2019 at 16:02

viraj892

3513 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Just a nice guy · Accepted Answer · 2021-08-10 15:28:01Z

14

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.

edited Aug 10, 2021 at 15:28

Just a nice guy

5985 silver badges20 bronze badges

answered Feb 25, 2016 at 5:29

MOCKBA

1,79011 silver badges20 bronze badges

3 Comments

mrQWERTY Over a year ago

Yes, I am using MySQL. That probably explains why. I will use an app level constructor check.

Sam Redway Over a year ago

What is an 'app level constructor check'?

user749127 Over a year ago

MySQL supports "check constraints" now: w3schools.com/sql/sql_check.asp

DSolei · Accepted Answer · 2020-10-11 19:27:40Z

An alternative way to create a check constraint is to use the event.listen event API from SQLAlchemy.

In my use case, I wanted to create a column check on my model where the column couldn't be a negative number.

event.listen(target, identifier, func)

The target in this case is my model ("Post") and my column ("upvotes"). The identifier in this case is "set". The function in this case is a static method created within my model. So it would look like this:

event.listen(Post.upvotes, "set", Post.before_set_upvotes)

My function is as follows:

@staticmethod
def before_set_upvotes(target, value, oldvalue, initiator):
    if value < 0:
        raise ValueError('Upvotes cannot be a negative value')

The target argument is "Post.upvotes" and value is the value we are setting the column attribute to be. NOTE: The function that is used in event.listen requires 4 arguments, I am unsure why so if someone has a reason please comment. Without 4 arguments (and just having target and value as args), I get the error TypeError: before_set_upvotes() takes 2 positional arguments but 4 were given.

Now you can test this by running a function that tried to set and commit a negative number to that column. A value error will be raised, thus creating the constraint we need.

Collectives™ on Stack Overflow

sqlalchemy, using check constraints

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related