Rename Primary Key constraint on Azure

Question

I'm trying to change primary key on a table in Azure storage. The plan is to create a new table, move data over and drop the old table. The problem is that I need all the constraints names to be the same as the old ones.

So I can't create a new table until I rename old table and all the constraints on that table. I have tried sp_rename on the constraint, but no luck:

sp_rename 'PK_dbo.ContactSessions', 'PK_dbo.ContactSessions_old', 'OBJECT'

gives me that:

Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

If I run sp_rename like that:

sp_rename 'PK_dbo.ContactSessions', 'PK_dbo.ContactSessions_old'

I get this error:

No item by the name of 'PK_dbo.ContactSessions' could be found in the current database 'testing_only', given that @itemtype was input as '(null)'.

I have seen this thread: How can I alter an existing Primary Key on SQL Azure? and tried

create clustered index [NEW_INDEX_NAME] on [ContactSessions] ([ContactSessionId]) 
    with (drop_existing=on);

and got the error:

Could not find any index named 'NEW_INDEX_NAME' for table 'ContactSessions'.

Are there other options to change the index name??

I know that is possible because I can right click on the key name in SSMS and rename the key, but I need it in the script.

Answer 1

It's a good practice to delimit all identifiers. In this particular case, you said the PK_dbo prefix was actually part of the index name. Therefore, the name has to be delimited appropriately, or it cannot be parsed unambiguously.

Also, what's actually being renamed is an index, not an object.

This should work after filling in the blanks:

sp_rename '[<schema>].[<table>].[PK_dbo.ContactSessions]', 'ContactSessions_old', 'INDEX';

---

I wouldn't use CREATE INDEX ... WITH(DROP_EXISTING = ON) for this because it will physically recreate the index, which is unnecessary in this scenario. This construct is best used for when the definition of the index needs to change while preserving the name.

Answer 2

I had exactly the same problem. For my case, it worked to place the old name between square brackets inside the quotes. So in your case, you might try:

sp_rename '[PK_dbo.ContactSessions]', 'PK_dbo.ContactSessions_old'