How do I add PRIMARY KEY/IDENTITY attributes to existing column in MS SQL 2000?
Posted on 2007-07-23
I'm having a problem with Microsoft SQL Server 2000 and am hoping to benefit from the wisdom of the experts out there. Here's what's going on...
After making a stupid mistake in a script I wrote and having to restore one of the tables from previous backups using EMC Retrospect, I realized that, while Retrospect restores all the data in a given table without a problem, attributes such as default values, primary key, etc. are stripped from all columns.
I was able to add the default values back to all columns using the query analyzer. But one of the columns in the restored table, called "acctid", is an identity column and primary key, and I do not know how to add those attributes back to the column, so that it will continue to auto increment its value every time a record is added to the table.
I'm not terribly familiar with Microsoft SQL Server, so if anyone can provide me with insight on how to write a SQL query that will set this column back to the primary key/identity column, I will be extremely grateful.