How to set check constraint in sql model?

christianholland commented 2 years ago

First Check

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

Commit to Help

[X] 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 commented 2 years ago

You can use various sa_column* args like:

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

jd-solanki commented 2 years ago

I use sa_column for Enum like below:

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

dannyrohde commented 2 years ago

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 commented 4 weeks ago

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.

fastapi / sqlmodel