Creating a Primary Key over an existing Unique, Clustered Index

• Lawrence M. Meklemburg

  Ten Centuries

  Points: 1227

  More actions

  June 4, 2008 at 6:51 am

  #124127

  I have 7 tables all associated with what a Primary Key on a table called Person. The column for the Primary Key is called Person_ID.

  The problem is when I restored the database from a SQL 2000 backup to SQL 2005 Enterprise Edition DBCC CheckDB reported errors in the database associated the Person table. I noticed that in error someone had made the Person_ID a non-clustered index (PK_PersonID), so I scripted a query to drop the index and create a new unique, clustered index over Person_ID. That fixed my DBCC CHECKDB error, but left me without a Primary Key on the Person table. How do I create the primary key on Person_ID for the Person table?

  Thanks,

  Larry

• Lawrence M. Meklemburg

  Ten Centuries

  Points: 1227

  More actions

  June 4, 2008 at 8:34 am

  #824220

  Dropped all the Foreign Keys that were associated with the Person primary key.

  Dropped the Unique Clustered Index over Person.

  Created the Primary Key as Clustered over Person_ID

  Added the Foreign key constraints back.

  Just wanted you to know.

  Thanks,

  Larry