troubleshooting Question

How do I add PRIMARY KEY/IDENTITY attributes to existing column in MS SQL 2000?

Avatar of alphact
alphact asked on
Microsoft SQL Server
6 Comments1 Solution5986 ViewsLast Modified:
Hello all,

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.

- Tristan
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros