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?

The Methods are referring to the 'tries' in the code above. I changed it all to "Method" to make it more clear. — user3685285, Apr 24 '17 at 17:05

score 21 · Answer 1 · answered Apr 24 '17 at 18:19

21

Try following solution:

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

Example:

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

answered Apr 24 '17 at 18:19

Bogdan Sahlean

19,233
3
42
57

SqlZim · Answer 2 · 2017-04-24T16:46:27.683

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.

edited Apr 24 '17 at 16:46

answered Apr 24 '17 at 16:12

SqlZim

37,248
6
41
59

You don't need to use `N'[_MyTable]'`, this will create a table called ... `[[_MyTable]]`. Just use `'_MyTable'`. `_MyTable` doesn't need escaping anyway, you can write `select * from doc._MyTable` – Panagiotis Kanavos Apr 24 '17 at 16:43
@PanagiotisKanavos Corrected – SqlZim Apr 24 '17 at 16:46

score -3 · Answer 3 · answered Apr 24 '17 at 16:10

-3

Cant you delete it from your original table and re-create it with the name you want?

ALTER TABLE dbo.YourOldTable
DROP CONSTRAINT YourConstraintname;

answered Apr 24 '17 at 16:10

GandRalph

590
2
10

This should be a comment, not an answer. Besides, there's nothing wrong with `sp_rename` – Panagiotis Kanavos Apr 24 '17 at 16:15
I didn't say there was. Deleting the index and reading with a different name is a perfectly valid, if not preferred, answer. – GandRalph Apr 24 '17 at 16:25
2

Actually it's a very bad and expensive answer. It means that the index has to be *rebuilt*, a very expensive operation. Primary keys are typically clustered index which means *the entire table* has to be converted to a heap then back to a clustered index structure – Panagiotis Kanavos Apr 24 '17 at 16:33
Isn't this the same as my second try? – user3685285 Apr 24 '17 at 16:36
@user3685285 sqlzim posted the correct answer. You have to use a three-part name if you use schemas other than `dbo`, ie `schema.table.name` – Panagiotis Kanavos Apr 24 '17 at 16:39

SQL Server : rename primary key

3 Answers3