SQL Server syntax to create clustered primary key

Question

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

MS. · Accepted Answer · 2012-06-27T14:14:04.257

20

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'

edited Jun 27 '12 at 14:14

answered Jun 27 '12 at 14:08

MS.

696
7
10

@IanBoyd I don't have 2008 to test. Does it preserve the Primary Key constraint OK on that or is this a new thing for 2012? – MS. Jun 27 '12 at 14:20
No, it worked fine - the primary key is still the primary key (in SQL Server 2008 R2) – Ian Boyd Jun 27 '12 at 14:37
@IanBoyd - Good stuff. I saw the other answers and began to doubt it! – MS. Jun 27 '12 at 14:38
Great! It solved my problem. I needed to move tables to a new filegroup. I thought the only way is to drop FK constrain, then PK constrain, then recreate index, and recreate both constrains back. Your solution is working so nice, and it is beautiful! Thank you. – Oct 17 '12 at 21:18
2

i was having this problem again today. Googling brought me to this question on SO. i went to upvote this useful question - *"You cannot upvote your own post"* *"Wait, what!?"* – Ian Boyd Oct 15 '13 at 13:38

score 0 · Answer 2 · answered Jul 05 '19 at 20:29

Although not applicable to your particular question, another possibility is that a person may be trying to add columns to the PK in an order other than they were in initially. If you are attempting to re-create the index from info in sys.indexes and sys.index_columns, make sure you are building based on the order of sys.index_columns.key_ordinal

SQL Server syntax to create clustered primary key

2 Answers2