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

Posted on 2007-07-23
Last Modified: 2008-07-09
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
Question by:alphact
    LVL 8

    Expert Comment

    alter table and add colum with identity constraint

    ALTER TABLE Yourtable
    ADD Yourcolumn IDENTITY(1,1) NOT NULL
    LVL 8

    Expert Comment

    or to make it a primary key

    ALTER TABLE Yourtable

    when you add this column, it will automatically assingn the unique keys for each column. in this way you can restore your primary key...but make sure that the data is same as before in your new table. even if the order is wont get teh same primary keys for rows
    LVL 35

    Expert Comment

    by:David Todd

    When recently commenting on a similar question
    I found that SQL wont allow adding not null columns without a default to an existing table.

    I wasn't trying this with identity's so I'm not sure if the above will work or not.

    LVL 68

    Accepted Solution

    You will have to have exclusive use of the table for a while.

    First, script out the existing table.  Keep that script.

    Then rename the table:
    EXEC sp_rename 'tableName', 'tableNameTemp'

    Edit the script to make the "acctid" column an IDENTITY column (and a PK if desired).  Run the script.

    Now, reload the table *but keeping the existing acctid values*.

    INSERT INTO tableName (acctid, of columns...)
    SELECT *
    FROM tableNameTemp

    -- if PK designation has not been done yet
    ALTER TABLE tableName
    ADD PRIMARY KEY (acctid)

    DROP TABLE tableNameTemp

    You can then use the table as usual, with the identity in place.
    LVL 68

    Expert Comment

    >> First, script out the existing table. <<
    Just the table definition, not the rows themselves :-) .  [Just to make sure no misunderstandings :-) .]

    Author Comment

    Scott, thank you very much for your help.  I greatly appreciate it.  Thank you, also, to everyone else who commented.  I now have the table back to normal (as in, how it was before the Retrospect backup software messed it up), and I've learned a couple new things in the process.

    Thanks again.

    - Tristan

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now