How to make a double foreign key as a primary key

[junoriosity]

First Check

• [X] I added a very descriptive title to this issue.
• [X] I used the GitHub search to find a similar issue and didn't find it.
• [X] I searched the SQLModel documentation, with the integrated search.
• [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
• [X] I already read and followed all the tutorial in the docs and didn't find an answer.
• [X] I already checked if it is not related to SQLModel but to Pydantic.
• [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• [X] I commit to help with one of those options 👆

Example Code

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

Description

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). However, 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.

Do you know how to fix that?

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

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.9

Additional Context

No response