Skip to content

Autogenerate creates duplicate commands for unique constraints #844

Closed

Description

@enkelli

Describe the bug

alembic revision --autogenerate creates duplicate lines for unique constraints in upgrade() method.
It does not affect actual upgrade in DB, but it looks strange in revision script.

Expected behavior
No duplicate records in revision script.

To Reproduce

After alembic init alembic I added the following code to alembic/env.py

import sqlalchemy as sa
import sqlalchemy.orm as orm 
...

Base = orm.declarative_base()

class TestTable(Base):
    __tablename__ = 'test_table'

    id = sa.Column(sa.Integer, primary_key=True)
    value = sa.Column(sa.String, unique=True)

target_metadata = Base.metadata

Error

After alembic revision --autogenerate I got the following upgrade script:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('test_table',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('value', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('value'),
    sa.UniqueConstraint('value') #  <--- This is duplicate
    )   
    # ### end Alembic commands ###

Versions.

  • OS: Arch Linux (5.12.2)
  • Python: 3.9.5
  • Alembic: 1.6.0+
  • SQLAlchemy: 1.4.15
  • Database: PostgreSQL 12.6 (Debian 12.6-1.pgdg100+1)

Additional context
This happens on version 1.6.0 and newer.
1.5.8 works as expected.

Have a nice day!

Activity

  1. changed the title [-]Autogenerate creates duplicate commands for constraints[/-] [+]Autogenerate creates duplicate commands for unique constraints[/+] on May 19, 2021
  2. sqla-tester commented on May 20, 2021

    @sqla-tester
    Collaborator

    Mike Bayer has proposed a fix for this issue in the master branch:

    Remove unique/index flags when copying table https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/2834

  3. zzzeek commented on May 20, 2021

    @zzzeek
    Member

    thanks for reporting

  4. mgasner commented on May 24, 2021

    @mgasner

    Believe that this fix may have broken some previously valid migrations -- we have observed https://github.com/dagster-io/dagster/blob/master/python_modules/libraries/dagster-postgres/dagster_postgres/alembic/versions/c63a27054f08_add_snapshots_to_run_storage.py#L36 failing on alembic 1.6.3 (passes on 1.6.2) with the following:

    sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "snapshots"
    [SQL: ALTER TABLE runs ADD FOREIGN KEY(snapshot_id) REFERENCES snapshots (snapshot_id)]
    --
    

    I don't have a minimal repro for this but will try to get one in the next couple of days.

  5. added a commit that references this issue on May 24, 2021
  6. zzzeek commented on May 24, 2021

    @zzzeek
    Member

    Believe that this fix may have broken some previously valid migrations -- we have observed https://github.com/dagster-io/dagster/blob/master/python_modules/libraries/dagster-postgres/dagster_postgres/alembic/versions/c63a27054f08_add_snapshots_to_run_storage.py#L36 failing on alembic 1.6.3 (passes on 1.6.2) with the following:

    sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "snapshots"
    [SQL: ALTER TABLE runs ADD FOREIGN KEY(snapshot_id) REFERENCES snapshots (snapshot_id)]
    --
    

    I don't have a minimal repro for this but will try to get one in the next couple of days.

    so crazy there were no tests for that, see #848

  7. added a commit that references this issue on May 25, 2021
  8. added a commit that references this issue on Jun 11, 2025
    acc82bf
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions