Primary + unique constraint on same migration

[Gildraen]

Describe the bug
Hi, when i want to create a table with a column that has primary AND unique:

id: MappedColumn[UUID] = mapped_column( UserIdType(as_uuid=True), primary_key=True, unique=True, nullable=False )

the migration generated seems good:
op.create_table( "users", sa.Column("id", UserIdType(), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("id"), )
but it does not create the uniq index.. i only have the primary index

Expected behavior
The creation of the table, with the primary index and the unique index on the same migration

To Reproduce

# Insert code here
from uuid import UUID
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy import Uuid
from sqlalchemy.orm import MappedColumn
from sqlalchemy.orm import mapped_column

class User(DeclarativeBase):
    __tablename__ = "users"

    id: MappedColumn[UUID] = mapped_column(
        Uuid(as_uuid=True), primary_key=True, unique=True, nullable=False
    )

Versions.

• OS: Debian 12
• Python: 3.11.9
• Alembic:1.13.1
• SQLAlchemy:2.0.29
• Database: Postgresql:16
• DBAPI:psycopg2

Have a nice day!

[CaselIT]

Hi,

I'm not sure what you are asking here. The generated migration is correct, so I'm not sure what you are look for

[Gildraen]

Hi, totally, the migration is correct, but the final result is not

I don't know if it's related to alembic or to postgresql

but the result is : there is no unique key constraint on the table, only the primary key

how can i see the raw sql generated from alembic ? maybe it could help for understanding ?

Here is the result of the migration as mentionned on op (to show that there is one constraint missing)

[Gildraen]

Based on that, is it usefull, intended, that alembic generate these migration with both constraint ?
Like, creating it, and let to the db the decision of what to do with it ?
or maybe there is some scenario where both would be taken in consideration even in pg ?

[CaselIT]

There is really no logic here regarding selecting what to render.
The migration has both so both are rendered

[Gildraen]

Okey, and it seems relevant for me, not the job of Alembic do determine those specifics choices (as long as it doesn't make the migration break ^^)

[CaselIT]

it's actually quite strange that pg decides here to not do something. usually is happy to let the user do what it pleases even if it does not make much sense or it's not needed. like having two indexes in the same column etc.

not sure why they decided to handle this case differently, considering that it then lets you create an unique constraint if you use alter table

[Gildraen]

Exactly, i still can add the constraint on a second hand, so yea, that's what i finded strange.. i may ask on their repo, who knows ! Maybe is not intended