Wednesday, January 19, 2011

Rename a constraint

If you need to rename a constraint after creating with typo or due to any other reason, you could follow the below steps.

--creating test table with PK constraint
CREATE TABLE dbo.RenameConstraintTest
(
ID int CONSTRAINT PKC_RenameConstraintTest PRIMARY KEY CLUSTERED
)
GO

--check the existance of the constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest'
GO

--rename the constraint
EXEC sp_rename N'[dbo].[RenameConstraintTest].[PKC_RenameConstraintTest]', N'PKC_RenameConstraintTest2', N'INDEX'
GO

--check the old constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest'
GO
--check the renamed constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest2'
GO

No comments:

Post a Comment

Transitioning of SQLOS to SQLPAL

​ The SQLOS​ Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...