Deleting a model that doesn't keep back references to its relationships -- with PR

[thedamnedrhino]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 Repository(SQLModel, table=True):
  id: int = Field(primary_key=True)
  ...

class User(SQLModel, table=True):
  ...
  name: str
  repository_id: int | None = Field(foreign_key='repository.id')
  repository: Repository | None = Relationship()


# -- first create a user without a repository
user = User(name='some user')
session.add(user)
session.flush()

# -- create and add the repository
rep = Repository()
user.repository = rep

session.add(rep)
session.flush()

session.commit()

# -- now delete the user's repository
session.delete(rep)
session.commit()

Description

The above code would raise the error:

psycopg2.errors.ForeignKeyViolation: update or delete on table "repository" violates foreign key constraint "user_repository_id_fkey" on table "user". Key (id)=(1) is still referenced from table "user" .

One workaround would be to add the back reference from Repository to User. In this case it makes sense, although for my use case it would clutter my models and make them less semantically coherent.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10.0

Additional Context

No response

[thedamnedrhino]

This is the sqlalchemy version of this question:
This is the same as this question for sqlalchemy: https://stackoverflow.com/questions/34497812/how-to-nullify-childres-foreign-key-when-parent-deleted-using-sqlalchemy
It solves the issue by supplying an ondelete option to the ForeignKey definition. #610 adds this capability.

[YuriiMotov]

The code example from initial post works fine (after fixing couple of obvious things):

See full code in the details:

from sqlmodel import Field, Relationship, SQLModel, create_engine, Session

class Repository(SQLModel, table=True):
  id: int = Field(primary_key=True)
  ...

class User(SQLModel, table=True):
  id: int = Field(primary_key=True)
  ...
  name: str
  repository_id: int | None = Field(foreign_key='repository.id')
  repository: Repository | None = Relationship()


sqlite_url = "sqlite:///"

engine = create_engine(sqlite_url, echo=True)


SQLModel.metadata.create_all(engine)

with Session(engine) as session:

    # -- first create a user without a repository
    user = User(name='some user')
    session.add(user)
    session.flush()

    # -- create and add the repository
    rep = Repository()
    user.repository = rep

    session.add(rep)
    session.flush()

    session.commit()

    # -- now delete the user's repository
    session.delete(rep)
    session.commit()


    session.refresh(user)
    print(user.repository)  # Should be None, as the repository was deleted

This section of docs explains how to use ondelete: https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/cascade-delete-relationships/