SQL Server syntax to create clustered primary key
sql-server, sql-server-2008-r2
Solution
CREATE UNIQUE CLUSTERED INDEX PK_AuditLog_AuditLogID
ON dbo.AuditLog(AuditLogID)
WITH DROP_EXISTING
ON [TheOtherFileGroup]
The logical primary key constraint is preserved (though tested in 2012)
CREATE TABLE dbo.AuditLog
(
AuditLogID int constraint PK_AuditLog_AuditLogID primary key
)
CREATE UNIQUE CLUSTERED INDEX PK_AuditLog_AuditLogID
ON dbo.AuditLog(AuditLogID)
WITH DROP_EXISTING
ON [Primary]
SELECT CONSTRAINT_TYPE /*Returns PRIMARY KEY*/
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME = 'PK_AuditLog_AuditLogID'
Problem
i want to move a table to a new filegroup. Using the accepted answer as a starting point: ``` CREATE CLUSTERED INDEX CIX_YourTable ON dbo.YourTable(YourClusteringKeyFields) WITH DROP_EXISTING ON [filegroup_name] ``` i adapt it to my use: ``` CREATE CLUSTERED INDEX PK_AuditLog_AuditLogID ON dbo.AuditLog(AuditLogID) WITH DROP_EXISTING ON [TheOtherFileGroup] ``` gives the error: Msg 1907, Level 16, State 1, Line 1 Cannot recreate index 'PK_AuditLog_AuditLogID'. The new index definition does not match the constraint being enforced by the existing index. This is, i assume, because `PK_AuditLog_AuditLogID` is - a clustered index - a primary key So i need the syntax to create a primary key clustered index `WITH DROP_EXISTING`