How to set check constraint in sql model?

[christianholland]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

pass

Description

SQLAlchemy allows to implement CheckContraint on either column or table level. How can I archive the same in SQLModel, e.g. limiting the possible values of a column to the colors red and green or to ensure that the values within a column are greater than 10.

I suppose it must be somehow possible using sa_column_kwargs (similar to implementing UniqueConstraint).

Help would be highly appreciated, thanks!

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.8.1

Additional Context

No response

[jd-solanki]

You can use various sa_column* args like:

• Field(sa_column_args=[CheckConstraint('col1>5')])
• Field(sa_column=Column(...))

[jd-solanki]

I use sa_column for Enum like below:

    access_status: EnumAccessStatus = Field(
        sa_column=Column(
            SaEnumAccessStatus,
            nullable=False,
        )
    )

[dannyrohde]

One caveat to using columns is that you might need to move properties like index or nullable into the Column description. I found that it otherwise doesn't get honoured.

E.g. the example in the above comment will work, but the below won't have the intended effect:

access_status: EnumAccessStatus = Field( sa_column=Column(SaEnumAccessStatus,), nullable=False, index=True)

[LumaC0]

How about CheckConstraint at the table level? I've been setting it in table_args like I'd do in sqlalchemy and just noticed Alembic isn't recognizing it.

[YuriiMotov]

One caveat to using columns is that you might need to move properties like index or nullable into the Column description. I found that it otherwise doesn't get honoured.
E.g. the example in the above comment will work, but the below won't have the intended effect:
access_status: EnumAccessStatus = Field( sa_column=Column(SaEnumAccessStatus,), nullable=False, index=True)

In current version, you will get a runtime error if you try using nullable or index with sa_column:

RuntimeError: Passing nullable is not supported when also passing a sa_column

Considering this comment from Sebastian

So I have intentionally simplified and limited the things I support in SQLModel, to work for the majority of use cases (say 90%) in a very simple and intuitive way (at least I hope so) without all the more advanced/exotic features that SQLAlchemy supports.

I think that sa_column is the only way to go..

[smiling-watermelon]

I know that that's pretty tangential to the question at hand, but, has anyone else encountered a problem where by setting sa_column after you've had a field defined without sa_column, when making a migration with alembic, sqlalchemy will raise an exception saying something like:

sqlalchemy.exc.ArgumentError: Column object 'amount' already assigned to Table 'balance'

I've encountered this error while setting CheckConstraint, so, it's sort of related to the question posted here, but not really 😅

UPD: This is related to the inheritance. I suppose table is set on Column and consequently - Field on definition, so, if you have a base model (like I had for balance), you'll have to duplicate the field in your child models 😢