0

WIth SQLModel in Python, I want to create one item of user and one of group associated to it.

class User(SQLModel, table=True):
    id: str = Field(primary_key=True, nullable=False)
    group: Optional["Group"] = Relationship(
        sa_relationship_kwargs={"uselist": False, "cascade": "save-update,merge,expunge,delete,delete-orphan"},
        back_populates="user")

class Group(SQLModel, table=True):
    group_id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True, index=True, nullable=False)
    user_id: Optional[str] = Field(default=None, foreign_key="user.id", nullable=True)
    user: Optional["User"] = Relationship(back_populates="group")

That works nicely when creating a user and an associated group, but when I want to delete the user, the associated group should be deleted as well. However it gives me the following error message

sqlalchemy.exc.IntegrityError: (sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class 'asyncpg.exceptions.ForeignKeyViolationError'>: update or delete on table "user" violates foreign key constraint "group_user_id_fkey" on table "group"
 DETAIL:  Key (id)=(abcd) is still referenced from table "group".
[SQL: DELETE FROM "user" WHERE "user".id = %s]
[parameters: ('abcd',)]

Do you know how to fix that issue?

tobias
  • 501
  • 1
  • 6
  • 15
  • Have you seen https://github.com/tiangolo/sqlmodel/issues/213#issuecomment-1140635500 ? Not sure if this is the case with the current versions, but this was broken not too long ago on specific versions. – MatsLindh Oct 05 '22 at 20:23
  • Hi @MatsLindh many thanks for getting back to me? Well, I went through that very thread, but it seems it is somewhat different from this one here. Do you happen to have some suggestion here? – tobias Oct 05 '22 at 20:32
  • I'm thinking about the last comment, which references another thread - i.e. a specific SQLAlchemy version borked this. Did you recreate the tables after adding the parameter? (in case the underlying engine implements this at creation time) – MatsLindh Oct 05 '22 at 20:48
  • Well, I am currently developing my fastAPI server with docker-compose, where a PostgreSQL container is attached. Hence, I am constantly tearing everything down and building it again. So that point should not be an issue. – tobias Oct 05 '22 at 20:54
  • Hello, I think the issue here is, you are trying to delete the user before deleting the group. Group has a foreign key constraint on the user table, so you first have to delete the group associated with the user and then delete the user. – Anitesh Reddy Oct 06 '22 at 12:24
  • @AniteshReddy many thanks for getting back to me. Yes that would be a feasible solution, but I am looking for something, that I can delete the user and the group is deleted automatically. Is that possible? – tobias Oct 06 '22 at 16:06

0 Answers0