ForeignKeyViolationError in Relationship deletion

[junoriosity]

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, 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")

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

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?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.9

Additional Context

No response

[antont]

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?

[junoriosity]

@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? 😀

[junoriosity]

@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?

[daniil-berg]

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?

[YuriiMotov]

You can read more about cascade delete relationships in docs: https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/cascade-delete-relationships/