Use uniqueidentifier as primary-key fails in combination with foreign keys

[matthiasburger]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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

from uuid import UUID
from typing import TYPE_CHECKING

from sqlmodel import Field, Column, Relationship, ForeignKey
from sqlalchemy.dialects.mssql import UNIQUEIDENTIFIER

from pydantic import BaseModel as PydanticBase

if TYPE_CHECKING:
    from . import Asset, Project

class GUIDModel(PydanticBase):
    guid: Optional[UUID] = Field(...,
                                 primary_key=True,
                                 sa_column=Column("guid", UNIQUEIDENTIFIER, nullable=False, primary_key=True, server_default=text("newsequentialid()")))


class AssetProjectMappingBase(BaseMappingModel):
    assetId: UUID = Field(foreign_key="Asset.guid", 
                          sa_column=Column("assetId", UNIQUEIDENTIFIER, nullable=False))

    projectId: UUID = Field(foreign_key="Project.guid", 
                            sa_column=Column("projectId", UNIQUEIDENTIFIER, nullable=False))

    asset: "Asset" = Relationship(back_populates="assetProjectMappings")
    project: "Project" = Relationship(back_populates="assetProjectMappings")

Description

• create a class A with PK of type UNIQUEIDENTIFIER
• create another class with FK to class A with type UNIQUEIDENTIFIER

specify the relation like this:

    projectId: UUID = Field(foreign_key="Project.guid", 
                            sa_column=Column("projectId", UNIQUEIDENTIFIER, nullable=False))
    project: "Project" = Relationship(back_populates="assetProjectMappings")

try to generate the alembic migration - alembic ignores the foreign key constraint.

if the types are like char(32) and generated uuid, it works - but as soon as you use sa_column it seems like the foreign_key parameter is ignored

Operating System

Linux, Windows, macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10.10

Additional Context

No response