How to specify Check Constraint on Varchar Datatype column in SQLALchemy?

Question

I am trying to specify this check constraint on a column via SQLAlchemy.

delete_flag char(1) not null CHECK(delete_flag in('Y','N')),

I am not able to figure out the syntax to achieve this. Any link, tutorial, advise will be of great help.

Thanks Tara Singh

Answer 1

You should be using the SQLAlchemy Enum type in this case.

import enum
from sqlalchemy import Enum
class YesNo(enum.Enum):
 yes = 'Y'
 no = 'N'
t = Table(
 'data', MetaData(),
 Column('value', Enum(YesNo, create_constraint=True))
)
connection.execute(t.insert(), {"value": YesNo.no})
assert connection.scalar(t.select()) is YesNo.no

Check Constraint docs for completeness

• baxx: Docs aren't very clear here - what would the actual code look like which answers the posted question?
• Mark Harviston: I've added the example, but it's literally the example from the docs with "one = 1, two = 2, three = 3" replaced with "yes = 'Y', no = 'N'" and create_constraint=True to create the constraint