Alembic Primary Key issue with mssql ( Azure Synapse SQL DW )

Question

Im trying to apply migrations using alembic to Azure Synapse SQL DW. Im facing following issue while performing alembic upgrade head:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Enforced unique constraints are not supported. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement. (104467) (SQLExecDirectW)')
[SQL: 
CREATE TABLE alembic_version (
        version_num VARCHAR(32) NOT NULL, 
        CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)

]
(Background on this error at: https://sqlalche.me/e/14/f405)

my version file is :

import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '6c51cb206ea6'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'dqrule',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('description', sa.String, nullable=False),
        sa.Column('source_type', sa.String, nullable=False),
        sa.Column('source_schema', sa.String, nullable=False),
        sa.Column('source_entity', sa.String, nullable=False)
    )


def downgrade():
    op.drop_table('dqrule')

You could try adding a line in the `[alembic]` section of alembic.ini that says `version_table_pk = false` and see if that helps. — Gord Thompson, Sep 28 '21 at 20:19

score 0 · Answer 1 · answered Sep 28 '21 at 19:43

A valid SQL statement for Azure Synapse Analytics, dedicated SQL pool would be:

CREATE TABLE alembic_version (
        version_num VARCHAR(32) NOT NULL, 
        CONSTRAINT alembic_version_pkc PRIMARY KEY NONCLUSTERED (version_num) NOT ENFORCED
);

As per the error message, dedicated SQL pools do not support unique constraints but you can create them with the NOT ENFORCED keywords. Clustered primary keys are also not supported so you have to add the NONCLUSTERED keyword.

You should probably also specify the distribution ( ROUND_ROBIN | HASH | REPLICATE ) and the index type ( CLUSTERED COLUMNSTORE INDEX | CLUSTERED INDEX | HEAP ) in a WITH clause, eg this statement will have the same outcome as the one above but is explicit and self-documenting:

CREATE TABLE alembic_version (
        version_num VARCHAR(32) NOT NULL, 
        CONSTRAINT alembic_version_pkc PRIMARY KEY NONCLUSTERED (version_num) NOT ENFORCED
)
WITH (
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

The choices you make depend on the data volumes and usage best desrcibed here. ROUND_ROBIN and CLUSTERED COLUMNSTORE INDEX are the defaults. Consider crafting these statements instead of using a framework if the framework will not give you the flexibility.

score 0 · Answer 2 · answered Sep 29 '21 at 12:20

I was able to suppress the creation of the primary key on the alembic_version table by doing the following:

Edit env.py and add the version_table_pk=False option to the context.configure() call in the run_migrations_online() function. That is, this …

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata
        )

… becomes this

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata,
            version_table_pk=False
        )

Alembic Primary Key issue with mssql ( Azure Synapse SQL DW )

2 Answers2