Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do you really have the PK named "PK_dbo.ContactSessions"? I believe that it doesn't find it because it sees the part of the name is the schema of the object, while the second is the object name. PS: in SSMS you can get the script of the operation you just did in designer (you have the option to generate to new file). –  Marian Jan 17 '13 at 14:01
    
@Marian Yes, I need it in that exact format. We are using some other tool (Entity Framework Migrations) to generate our schema. And that tool would be broken with different name for the constraint. I'll try getting the output to the screen - thanks for the tip. –  trailmax Jan 17 '13 at 14:27
    
For sp_rename, (a) use the object type INDEX, and (b) the new name should be the object name only, without a schema. I don't have Azure handy to test this, but it works fine on a standalone instance. –  Jon Seigel Jan 17 '13 at 14:37
    
@JonSeigel Run this: sp_rename 'PK_dbo.ContactSessions', 'ContactSessions_old', 'INDEX' and got the same error: "Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong." –  trailmax Jan 17 '13 at 14:45
2  
If that's the case, you have to delimit the identifier. sp_rename '[schema].[table].[PK_dbo.ContactSessions]' .... –  Jon Seigel Jan 17 '13 at 14:53
show 3 more comments

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.