How to make a double foreign key as a primary key in SQLModel resp. SQLAlchemy

Question

Currently, I have two tables, user and groups and I want to associate them in table group2user, where I specify who has which rights to a group table.

Hence, I need two foreign keys in group2user, which should be able to do cascading delete (if we delete the user or group item).

For this, I wrote down the following code with SQLModel and SQLAlchemy

import enum
from typing import Optional
from sqlmodel import SQLModel, Field, Relationship

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

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(sa_column=Column(String, ForeignKey("user.user_id", ondelete="CASCADE")))
    user_list: List["Group2User"] = Relationship(
        sa_relationship_kwargs={"cascade": "save-update,merge,expunge,delete,delete-orphan"},
       )

class GroupRights(enum.Enum):
    READ = "read"
    WRITE = "write"
    ADMIN = "admin"


class Group2User(SQLModel):
    user_id: str = Field(sa_column=Column(String, ForeignKey("user.user_id", ondelete="CASCADE"), nullable=False, primary_key=True))
    group_id: uuid.UUID = Field(sa_column=Column(UUID, ForeignKey("group.group_id", ondelete="CASCADE"),
                                                 primary_key=True, nullable=False))
    rights: GroupRights = Field(default="READ")

When I have a look at the tables (see below), I see the cascading delete for group via foreign key user_id. However, the same does not apply for user_id and group_id in the table group2user, where it is a primary key, but not a foreign key with ON DELETE CASCADE.

CREATE TABLE "user" (
    user_id VARCHAR NOT NULL,
    PRIMARY KEY (user_id)
)


CREATE TABLE "group" (
    user_id VARCHAR,
    group_id UUID NOT NULL,
    PRIMARY KEY (group_id),
    FOREIGN KEY(user_id) REFERENCES "user" (user_id) ON DELETE CASCADE
)


CREATE TABLE group2user (
    user_id VARCHAR NOT NULL,
    group_id UUID NOT NULL,
    rights grouprights NOT NULL,
    PRIMARY KEY (user_id, group_id)
)

Do you know how to fix that?

relent95 · Accepted Answer · 2022-10-11T00:55:11.097

If you want a many to many relationship, you should use the link_model option on Relationship members, like the following.

class Group2User(SQLModel, table=True):
    ...

class User(SQLModel, table=True):
    ...
    groups: List['Group'] = Relationship(back_populates='users', link_model=Group2User)

class Group(SQLModel, table=True):
    ...
    users: List[User] = Relationship(back_populates='groups', link_model=Group2User)

See the official tutorial for a detail.

If you want an association object relationship, you should define bidirectional Relationship members like the following.(I renamed Group2User to Acl for better readability. ACL means access control list.)

class Acl(SQLModel, table=True):
    ...
    user: 'User' = Relationship(back_populates='acls')
    group: 'Group' = Relationship(back_populates='acls')

class User(SQLModel, table=True):
    ...
    acls: List[Acl] = Relationship(back_populates='user')

class Group(SQLModel, table=True):
    ...
    acls: List[Acl] = Relationship(back_populates='group')

See the official tutorial for a detail.

If you want cascade deletions on the DBMS level, you should do like this.(I changed the name and type of the primary key columns for better readability.)

class Acl(SQLModel, table=True):
    user_id: int = Field(sa_column=
        Column(Integer, ForeignKey('user.id', ondelete='CASCADE'),
            primary_key=True))
    group_id: int = Field(sa_column=
        Column(Integer, ForeignKey('group.id', ondelete='CASCADE'),
            primary_key=True))
    ...
    user: 'User' = Relationship(back_populates='acls')
    group: 'Group' = Relationship(back_populates='acls')

class User(SQLModel, table=True):
    ...
    acls: List[Acl] = Relationship(back_populates='user',
        sa_relationship_kwargs = dict(cascade='all', passive_deletes=True))

class Group(SQLModel, table=True):
    ...
    acls: List[Acl] = Relationship(back_populates='group',
        sa_relationship_kwargs = dict(cascade='all', passive_deletes=True))

See the SQLAlchemy documentation for a detail.

As a side note, SQLModel project is in its pretty early stage at this time.(Even it has no API reference.) I don't recommend it if you are not ready to hack the source code.

Many thanks for getting back to me. Yes, I saw that too, but I do need extra fields (https://sqlmodel.tiangolo.com/tutorial/many-to-many/link-with-extra-fields/) and what is somewhat worrying me here, is that the cascading delete is not possible here anymore, which I do like quite a lot. Is there a way to make that possible here? — tobias, Oct 09 '22 at 06:42
Many thanks again, I tried out approach no. 3, sadly this is what I got for the Group2User table: `Indexes: "group2user_pkey" PRIMARY KEY, btree (user_id, group_id)`, but not foreign key relationships. — tobias, Oct 09 '22 at 18:44
You need to provide a [mre] with a debugging detail(including your database driver). Your code in the question results in a name error. — relent95, Oct 10 '22 at 01:44
My code did not work because I was having it in two classes like `class AclBase` which is `user_id` and `group_id` and then an extension `class Acl` with the relationships, but this has to be avoided. Many thanks for your help. — tobias, Oct 10 '22 at 18:55

How to make a double foreign key as a primary key in SQLModel resp. SQLAlchemy

1 Answers1