Creating a Primary Key over an existing Unique, Clustered Index

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Notification Services » Administration » Creating a Primary Key over an existing...

Creating a Primary Key over an existing Unique, Clustered Index

Rate Topic

Display Mode

Topic Options

Author

Message

Lawrence M. Meklemburg

Posted Wednesday, June 04, 2008 6:51 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:56 PM
Points: 987, Visits: 124

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

Post #511316

Lawrence M. Meklemburg

Posted Wednesday, June 04, 2008 8:34 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:56 PM
Points: 987, Visits: 124

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

Post #511427

« Prev Topic | Next Topic »

Permissions