Alembic doesn't detect adding unique constraints

[matthiasburger]

Describe the bug
Alembic doesn't detect adding unique constraints. Hint, I'm not using default schema.

Expected behavior
if I add unique=True for single-columns or UniqueConstraint("col1", "col2") into __table_args__ it should generate the unique constraints into migration-file

To Reproduce
I'm using tiangolo sqlmodel and in my database-model I wanted to add unique-constraints, so one is kind of a business-key and the other is for mapping tables - both aren't recognized by alembic.

The one for the business-key is in a base-table (because all non-mapping-tables inherit from this class) and looks like this:

class BusinessKeyModel(PydanticBase):
    businessKey: Optional[str] = Field(
        alias="businessKey",
        max_length=255,
        description=DescriptionConstants.BUSINESS_KEY,
        nullable=True,
        unique=True  # <-- added this before generating new migration
    )

class BaseTableModel(SQLModel, BusinessKeyModel):
    ...

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

so when I now add unique=True to the BusinessKeyModel.businessKey and try to generate a new migration with alembic, (with autogenerate) it doesn't detect the changes.

Same goes for my mapping-tables, after I added UniqueConstraint into my __table_args__ I think it should detect the changes:

class UserRoleMappingBase(BaseMappingModel, GUIDModel):

    userId: UUID
    roleId: UUID


class UserRoleMapping(UserRoleMappingBase, table=True):
    __table_args__ = (
        UniqueConstraint("userId", "roleId"), # <-- added this before generating new migration
        {"schema": "dbx_v2"}
    )

Versions.

• OS: Mac Ventura 13...
• Python: 3.9
• Alembic: 1.10.2
• SQLAlchemy: 1.4.41
• Database: SQL Server
• DBAPI:

Have a nice day!
Have a nice day too :)

[CaselIT]

Hi

I think sqlalchemy does not currently reflect any unique constraint in sql server, so that may be the reason for it.

moving to discussion

[matthiasburger]

well seems like sometimes it does... when I remove the schema in the __table_args__, it generates sth like drop table dbx_v2. UserRoleMapping and create table UserRoleMapping (without schema), and the new table then contains the unique constraint.

[CaselIT]

I believe the issue is only when adding unique constraints to existing tables.

IIRC the reason why sqlalchemy does not reflect unique constraint in sqlserver is that there is no way of distinguishing them from unique indexes.

Maybe there is an missing feature in alembic though, since it could probably consider unique constraints as if they were unique indexes when checking mssql for changes. @zzzeek do you remember if something like this was ever considered of if it never came up?