How to accomplish Read/Write transactions with a one to many relationship

[br-follow]

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

class User(SQLModel):
  __tablename__ = "users"
  id: Optional[str]
  cars: List[Car] = Relationship(sa_relationship=RelationshipProperty("Car", back_populates="user")

class Car(SQLModel):
  ...
  user_id: str = Field(default=None, foreign_key="users.id")
  user: User = Relationship(sa_relationship=RelationshipProperty("User", back_populates="cars"))
  is_main_car: bool

Description

I have two tables that have a many to one relationship, such as the one described above. Any given user can only have a single car that is_main_car. Additionally, the first car a user gets must be the main car.

I am trying to determine how the transactional semantics work with this relationship within a Session. If I read the user, and then use the user.cars field to determine if the user has 0 cars or already has a main car, can I rely on that condition still being true when I write my new main Car row to the Cars table (assuming it is all within a single Session)?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.7

Additional Context

No response