SQL Server : rename primary key

Question

I have a table doc.MyTable which I want to deprecate by renaming to doc._MyTable. I then want to create a new doc.MyTable with the same primary key that the old doc.MyTable had. The problem is that SQL Server says that primary key already exists. So that means I need to rename the old primary key too.

I tried the following:

EXEC SP_RENAME 'doc.MyTable', '_MyTable'

-- Method 1
EXEC SP_RENAME 'PK_MyTable', 'PK__MyTable'

-- Method 2
ALTER TABLE [doc].[_MyTable] DROP CONSTRAINT [PK_MyTable]
ALTER TABLE [doc].[_MyTable] ADD CONSTRAINT [PK__MyTable]
PRIMARY KEY CLUSTERED
(
    [document_id] ASC,
    [line_id] ASC,
    [sub_line_id] ASC
)

-- Create new table
CREATE TABLE [doc].[MyTable] (
    ... columns
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED (
    ... key columns
)
... extra conditions

Method 1 throws this error:

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

While method 2 throws this:

Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'PK.MyTable'.
The duplicate key value is (10358930, 336000, 0).`

When I try to create the new primary key for the new table.

I'm only using one of the two "Methods" at a time. How do I fix the issue?

Answer 1

Try following solution:

EXEC sp_rename '[TableSchema].[TableName].[ConstraintName]', 'NewConstraintName'

Example:

EXEC sp_rename '[doc].[_MyTable].[PK_MyTable]', '[PK__MyTable]'

Answer 2

When renaming your primary key, prefix the primary key name with the schema and table name like so:

create schema doc authorization dbo;
go
create table doc.MyTable (
    id int not null
  , constraint pk_MyTable primary key clustered (Id)
);
exec sp_rename N'doc.MyTable.pk_MyTable', N'pk__MyTable';
exec sp_rename N'doc.MyTable', N'_MyTable', N'object';
create table doc.MyTable (
    id int not null
  , constraint pk_MyTable primary key clustered (Id)
);

rextester demo: http://rextester.com/OBIB87116

If you were using the default schema dbo, you would not need to prefix the schema and table name to rename the primary key with sp_rename.