SQL Server: Rename Primary Key
I have a table doc.MyTable
that I want to reject by renaming it to doc._MyTable
. Then I want to create a new one doc.MyTable
with the same primary key as the old one doc.MyTable
. The problem is that SQL Server says the primary key already exists. This means that I also need to rename the old primary key.
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:
There is no item named "PK_MyTable" in the current database "db_dev", given that @itemtype was entered as "(null)".
Whereas method 2 throws this out:
Violation of PRIMARY KEY constraint 'PK_MyTable'. Unable to insert duplicate key into PK.MyTable object.
The duplicate key value is (10358930, 336000, 0) .`
When I try to create a new primary key for a new table.
I only use one of the two "Methods" at a time. How do I fix the problem?
source to share
When renaming a primary key, prefix the primary key name with the schema and table names as follows:
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)
);
Demo version of the rexter: 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
.
source to share