How can we rename the primary key of a table in FluentMigrator?

Question

I need to rename the primary key of an existing table through FluentMigrator so an automapper can automatically detect the column.

For most columns, it's a simple 1) delete any foreign key constraints on that column 2) delete indices for that column and 3) rename the column. I have historically done this by:

Delete.ForeignKey("foreignkeyconstraint").OnTable("mytable");
Delete.Index("UserId").OnTable("mytable");
Rename.Column("UserId").OnTable("mytable").To("UserInfo_id");

However, this doesn't appear to work for primary keys, since I can't delete the automatically created index on that column. What is the correct way to rename a primary key column with FluentMigrator?

Cheep

Answer 1

Use the following method call to rename your primary key (SQL Server)

Execute.Sql("EXEC sp_rename N'[Current_Primary_Key_Name]', '[New_Primary_Key_Name]', 'object';");

Distinction

Answer 2

Something like this should work as long as it is not an identity (auto increment) column as well:

Delete.PrimaryKey("PRIMARY KEY").FromTable("mytable");

Anallise

As of writing this will fail in Azure Database, if the primary key is the only clustered index on the table. I haven't found a workaround yet at this stage, and have just left the name of the primary key unchanged. This may not be a solution for the question, though. – Glosso

One of the advantages of labilbe's answer is sp_rename will handle that for you. See MSDN. – Ragamuffin

@MichaelMinton Won't work for mysql though (as per original question) – Anallise

@DanielLee True. Also worth noting that latest FluentMigrator's Rename.Column method will use sp_rename on MSSQL. – Ragamuffin