Consider a database with three tables, Parent, ChildTypeA, and ChildTypeB. Parent has the fields id, and child_type (an ENUM of “a” and “b”), and a unique constraint over both fields. ChildTypeA and ChildTypeB both have the field supertype_key (the aforementioned ENUM), and parent_id. They also have a composite unique foreign key of (parent_id, supertype_key) to (Parent.id, Parent.child_type).

(Let’s also pretend the child types have some more fields that are different to each other).

With SQLModel, I believe that would be expressed something like the following, but I can’t get the Relationship definition on Parent to work.

from enum import Enum
from typing import Union

from sqlalchemy.schema import ForeignKeyConstraint, UniqueConstraint
from sqlmodel import Field, Relationship, SQLModel


class ChildType(str, Enum):
    A = "a"
    B = "b"


class Parent(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    child_type: ChildType
    # child: Union["ChildTypeA", "ChildTypeB"] = Relationship(
    #     back_populates="parent",
    # )

    __table_args__ = (
        UniqueConstraint("id", "child_type"),
    )


class ChildTypeA(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    supertype_key: ChildType = Field(sa_column_kwargs={
        "server_default": ChildType.A.name,
    })
    parent_id: int = Field(unique=True)
    parent: Parent = Relationship(back_populates="child")

    some_special_A_type_field: int

    __table_args__ = (
        ForeignKeyConstraint(
            ["parent_id", "supertype_key"],
            ["parent.id", "parent.child_type"],
        ),
    )


class ChildTypeB(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    supertype_key: ChildType = Field(sa_column_kwargs={
        "server_default": ChildType.B.name,
    })
    parent_id: int = Field(unique=True)
    parent: Parent = Relationship(back_populates="child")

    some_special_B_type_field: str

    __table_args__ = (
        ForeignKeyConstraint(
            ["parent_id", "supertype_key"],
            ["parent.id", "parent.child_type"],
        ),
    )

Evaluating the models above with the following code:

from sqlalchemy import create_mock_engine
from sqlmodel import SQLModel

import the_above_file


def metadata_dump(sql, *args, **kwargs):
    print(sql.compile(dialect=engine.dialect))

engine = create_mock_engine("postgresql://", metadata_dump)

SQLModel.metadata.create_all(engine)

Prints the following expected SQL:

CREATE TYPE childtype AS ENUM ('A', 'B')

CREATE TABLE parent (
    id SERIAL NOT NULL, 
    child_type childtype NOT NULL, 
    PRIMARY KEY (id), 
    UNIQUE (id, child_type)
)



CREATE TABLE childtypea (
    id SERIAL NOT NULL, 
    supertype_key childtype DEFAULT 'A' NOT NULL, 
    parent_id INTEGER NOT NULL, 
    "some_special_A_type_field" INTEGER NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(parent_id, supertype_key) REFERENCES parent (id, child_type), 
    UNIQUE (parent_id)
)



CREATE TABLE childtypeb (
    id SERIAL NOT NULL, 
    supertype_key childtype DEFAULT 'B' NOT NULL, 
    parent_id INTEGER NOT NULL, 
    "some_special_B_type_field" VARCHAR NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(parent_id, supertype_key) REFERENCES parent (id, child_type), 
    UNIQUE (parent_id)
)

However, uncommenting the relationship definition Parent.child results in the following:

...
  File "/app/app/the_above_file", line 13, in <module>
    class Parent(SQLModel, table=True):
  File "/usr/local/lib/python3.10/site-packages/sqlmodel/main.py", line 524, in __init__
    relationship_to = get_relationship_to(
  File "/usr/local/lib/python3.10/site-packages/sqlmodel/_compat.py", line 149, in get_relationship_to
    raise ValueError(
ValueError: Cannot have a Union of None and None as a SQLAlchemy field

I found this issue resolving comment in SQLModel that addresses this desire for Field()s, but not for Relationship()s.