alembic alter_column with existing primary key constraint

Question

I need to alter the type of a column with alembic. Currently my code looks like this:

with op.batch_alter_table('annotations', schema=None) as batch_op:         
    batch_op.alter_column('anno_id', existing_type=sa.Integer(), type_=sa.String(36))

My issue is that anno_id is the table's primary key, and while the code works fine with sqlite, MS SQL rejects the command: The object 'PK__annotati__2CC37C988C804EF7' is dependent on column 'anno_id'.DB-Lib error message 20018, severity 16

The PK constraint was auto-generated so I can't know in advance what its name is. What do I need to do to identify the constraint and remove and then recreate it after altering the column?

UPDATE, based on Aaron Bertrand's answer below, I was able to do the following in alembic. Unfortunately, as he notes, I couldn't directly alter the column to change it to string type, so I dropped/recreated it.

        if context.get_impl().bind.dialect.name == "mssql":
            batch_op.execute("""
            DECLARE @sql    nvarchar(max) = N'',
        @table  nvarchar(513) = QUOTENAME(N'annotations'),
        @column nvarchar(128) = QUOTENAME(N'anno_id');

SELECT @sql += N'ALTER TABLE dbo.' 
    + @table + N' DROP CONSTRAINT ' 
    + QUOTENAME(name) + N';'
  FROM sys.key_constraints
  WHERE type = N'PK' AND parent_object_id = OBJECT_ID(N'dbo.' + @table);

EXEC sys.sp_executesql @sql;
            """)
        batch_op.drop_column('anno_id')
        batch_op.add_column(sa.Column('anno_id', sa.String(36), nullable=False))
        batch_op.create_primary_key('annotations_pkey', ['anno_id'])

*"The PK constraint was auto-generated so I can't know in advance what its name is."* This is why it's important to *explicitly* names your constraints. — Thom A, Apr 03 '22 at 16:38
Then perhaps you should invest the time to fix your object names (and any DDL statements you have for deployments). — Thom A, Apr 03 '22 at 16:47
So is your problem that you need to remove the constraint (PK or any others) first before you alter the column? The "naming" issue is just a red herring? — SMor, Apr 03 '22 at 16:52
Yes- I need to drop the PK constraint, alter the column, and recreate it. — Robert Sim, Apr 03 '22 at 16:58

Aaron Bertrand · Accepted Answer · 2022-04-03T17:11:51.357

The PK constraint was auto-generated so I can't know in advance what its name is

You can always change it (and change whatever code generates tables so it doesn't leave these names up to the database to assign).

EXEC sys.sp_rename 
     N'PK__anno_3213E83F046D1D25', -- you'll have to look this up once 
     N'PK_annotations', 
     N'OBJECT';

If you can't fix this design flaw in the database then you can look it up when you need to:

SELECT name FROM sys.key_constraints
  WHERE type = N'PK'
    AND parent_object_id = OBJECT_ID(N'dbo.annotations');

You can use that to generate a drop/create command so that you can alter any affected columns, but it will need some additional context (like what column(s) do the PK apply to). An example might be:

DECLARE @sql    nvarchar(max) = N'',
        @table  nvarchar(513) = QUOTENAME(N'annotations'),
        @column nvarchar(128) = QUOTENAME(N'anno_id');

SELECT @sql += N'ALTER TABLE dbo.' 
    + @table + N' DROP CONSTRAINT ' 
    + QUOTENAME(name) + N';'
  FROM sys.key_constraints
  WHERE type = N'PK' AND parent_object_id = OBJECT_ID(N'dbo.' + @table);

SELECT @sql += char(13) + char(10) + N'ALTER TABLE dbo.' 
    + @table + N' ALTER COLUMN ' 
    + @column + N' decimal(14,4) NOT NULL;'
  FROM sys.key_constraints
  WHERE type = N'PK'   
    AND parent_object_id = OBJECT_ID(N'dbo.' + @table);

SELECT @sql += char(13) + char(10) + N'ALTER TABLE dbo.' 
    + @table 
    + N' ADD CONSTRAINT PK_Annotations PRIMARY KEY(' + @column + N');';

PRINT @sql;
--EXEC sys.sp_executesql @sql;

Which will generate SQL like this (which you can PRINT to verify before commenting out the EXEC).

ALTER TABLE dbo.[annotations] DROP CONSTRAINT [PK__annotati__2CC37C98902B2CC2];
ALTER TABLE dbo.[annotations] ALTER COLUMN [anno_id] decimal(14,4) NOT NULL;
ALTER TABLE dbo.[annotations] ADD CONSTRAINT PK_Annotations PRIMARY KEY([anno_id]);

Working example on db<>fiddle

This is to run in a tool like SQL Server Management Studio. I highly doubt that you'll be able to build something this directly from alembic; ORMs are not exactly known for handling all use cases, only the bare minimum. As soon as you need to do something even remotely outside the bounds of bare minimum, you need to leave the ORM.

And this won't be the end. Because I assume that even if you aren't attempting to change the column in a way that could make SQL Server think there will be data loss (e.g. changing numeric(14,4) -> numeric(12,2)), you will also get blocked from dropping the PK if there things like foreign keys pointing to it, indexed views against the table, etc.

alembic alter_column with existing primary key constraint

1 Answers1