Repository navigation
ForeignKeyViolationError in Relationship deletion #457
Description
Activity
Yep it's because you have
foreign_key="user.id"in Group, so you can't delete a user when a group refers to it.You have defined cascades however, maybe it needs to be in the relationship in Group with back_populates though?
@antont Many thanks for getting back to me. Since, I am not very experienced here, could you adapt the code from above, such that I could try it out? 😀
@antont Here is what I tried now
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( sa_relationship_kwargs={"cascade": "save-update,merge,expunge,delete,delete-orphan"}, back_populates="group")and tried to post a user, I got
sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class Group->group'. Original exception was: For many-to-one relationship Group.user, delete-orphan cascade is normally configured only on the "one" side of a one-to-many relationship, and not on the "many" side of a many-to-one or many-to-many relationship. To force this relationship to allow a particular "User" object to be referred towards by only a single "Group" object at a time via the Group.user relationship, which would allow delete-orphan cascade to take place in this direction, set the single_parent=True flag. (Background on this error at: https://sqlalche.me/e/14/bbf0)Also, when using the same situation without
delete-orphan, i.e.,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( sa_relationship_kwargs={"cascade": "save-update,merge,expunge,delete,delete"}, back_populates="group")I still get
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',)]Does that help here?
I already checked if it is not related to SQLModel but to SQLAlchemy.
Did you really though? Could you show the SQLAlchemy equivalent code that you tried to make sure that this behavior is any different with SQLAlchemy?
- locked and limited conversation to collaborators
on Aug 7, 2025
First Check
Commit to Help
Example Code
Description
I want to create one item of user and one of group associated to it. 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
Do you know how to fix that issue?
Operating System
macOS
Operating System Details
No response
SQLModel Version
0.0.8
Python Version
3.9
Additional Context
No response