Cant remove identity attribute from PK

Question

I need to change the data type of the primary key on one of my tables to string from int (I didn't say I WANT to...). The migration generates this bit of code:

AlterColumn("dbo.Venues", "ID", c => c.String(nullable: false, maxLength: 128));

This causes SQL server to complain that the data type must be int, bigint, etc because the column is an identity column. I added the following but it appears to have no effect:

AlterColumn("dbo.Venues", "ID", c => c.Int(identity:false));

Question: How do I get the migration to change the data type from int to string?

The following may be related to my question but not necessarily: The only code generated by the migration is the first AlterColumn shown above. When run as natively generated, the migration caused SQL Server to complain that it could not alter the column because there were dependencies on it. In fact the only dependency is that it is a PK (there are no tables that reference it as a FK). I modified the migration to appear as follows and now I am getting the data type error as indicated above.

DropPrimaryKey("dbo.Venues");
AlterColumn("dbo.Venues", "ID", c => c.Int(identity:false));
AlterColumn("dbo.Venues", "ID", c => c.String(nullable: false, maxLength: 128));
AddPrimaryKey("dbo.Venues", "ID");

score 5 · Answer 1 · edited May 23 '17 at 12:17

At least for some versions of SQL Server, this can be done without dropping/recreating the database. It reportedly doesn't work for Azure (or didn't as of a 2015 comment). Inspired by this answer, here's my Up() method.

Note that I don't have any foreign keys on this table so don't need to support that wrinkle covered in the linked answer

    public override void Up()
    {
        DropPrimaryKey("dbo.Products"); // Should be same as ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;
        Sql("alter table dbo.Products add tempId int NOT NULL default -1");
        Sql("update dbo.Products set tempId = Id;");
        // Won't work. Can't remove identity: AlterColumn("dbo.Products", "Id", c => c.Int(nullable: false));
        Sql("alter table dbo.Products drop column Id");
        Sql("EXEC sp_rename 'Products.tempId', 'Id', 'COLUMN'");
        AddPrimaryKey("dbo.Products", "Id"); // Should be same as ALTER TABLE yourTable ADD CONSTRAINT PK_yourTable_id PRIMARY KEY (id)
    }

This worked like a charm for me and prevented me from having to drop any tables. — Baguazhang, Nov 28 '17 at 17:14
I want to find every post that says this is impossible and link to your answer. Thank you! — Moby Disk, Jul 29 '19 at 17:45

score 4 · Accepted Answer · answered Nov 22 '13 at 21:34

Removing an identity specification from a column can only be done by re-creating the table. Look at the ALTER TABLE - ALTER COLUMN statement: there is no syntax to change (add or remove) an identity specification.

So I'm afraid you'll have to do this manually and make a fresh start for subsequent migrations.

BTW Sql Server Management Studio won't help you doing this. You may want to use Toad for Sql Server. It creates a full table rebuild script when you remove an identity specification.

Added an answer that doesn't require re-creating the table. – Eric J. Apr 28 '17 at 20:55 — Eric J., Apr 28 '17 at 20:55

Cant remove identity attribute from PK

2 Answers2

Linked

Related