CheckConstraint
on a table that takes a combination of Fields: is there a way to enforce one of two Fields not being null?
#799
Unanswered
charlie-corus
asked this question in
Questions
Replies: 1 comment 1 reply
-
One solution is to use three different models: a class BasicHero(SQLModel):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
secret_name: str
class SoloHero(BasicHero, table=True):
homebase_id = Column(Integer, ForeignKey('homebase.id'))
homebase = relationship("Homebase")
class TeamHero(BasicHero, table=True):
team_id = Column(Integer, ForeignKey('team.id'))
team = relationship("Team") The constraints can then be enforced separately. The problem with this is that I have to restructure my database. I guess there are areas where the ORM and relational way of thinking just differ, and we have to adapt our design patterns if we want to take advantage of the ORM niceness. |
Beta Was this translation helpful? Give feedback.
1 reply
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
First Check
Commit to Help
Example Code
Description
I'd like to know if it's possible to set a
CheckConstraint
to a table that can apply some sort of combinatorial logic to multiple fields. Specifically, I'd like to enforce that exactly one of two fields is not null (orNone
).Let's say I want to make sure that if a hero isn't in a team then they have a base of their own to call home. Equally, if they are in a team, they shouldn't be wasting valuable hero resources on a separate base.
In SQL this would look something like
In SQLAlchemy it's possible to add a check constraint to the table. Here's a working example (with "base" renamed to "homebase" to avoid clashing with SQLAlchemy convention):
As expected, this fails with the helpful message
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) CHECK constraint failed: A Hero needs a place to hang their cape!
. Note that the failure is triggered from the backend, at commit time.I see that
SQLAlchemy.CheckConstraint
is exposed by the SQLModel API, and aCheckConstraint
can be passed straight to aField
constructor as of PR #436. This doesn't work for me - I need the CheckConstraint to take two Fields, so it needs to operate on the table. The approach that works in the SQLAlchemy ORM - creating aCheckConstraint
in a field called__table_args__
- passes type and syntax checks in my editor, but fails at runtime because theField
objects don't seem to have access to the same logical operators that exist for a SQLAlchemy column (likeisnot()
; see Example Code above).I can get SQLModel to create the constraint on the database by passing a string construct as follows:
This is less than ideal because the constraint is only applied in the backend when data is committed to the DB, so I wouldn't get to use it for validating inputs. I can't see a way to apply this sort of logic in a Pydantic model, but I'm very new to this whole ecosystem so apologies if I've overlooked something there.
Does SQLModel support, or plan to support, this sort of business? Do I have to restructure my database to fit into the ORM?
Operating System
macOS
Operating System Details
No response
SQLModel Version
0.0.14
Python Version
Python 3.12.1
Additional Context
No response
Beta Was this translation helpful? Give feedback.
All reactions