Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Posts
    1

    Unanswered: attach IDENTITY property to an existing column

    Hi All,
    Can any body tell me that how we can attach IDENTITY property to an existing int column

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you can not.

    you have to...

    1. rename the existing table
    2. drop the foriegn keys referencing it.
    3. drop the constraints on the renamed table.
    4. define a new table with the identity property with the orginal table name
    --the next 2 can go in any order based on what you decide is best
    5. then reapply all constraints on the table, usually doing the primary key first.
    6. insert data from the renamed table to the new table. you may want to do a INSERT IDENTITY MyTable ON to insert your existing keys. Just do not forget to turn it off when you are done.
    7. drop the renamed table (optional)

    you may want to wrap all or part of this in an explicit transaction with appropriate error handlers and print messages and you may not want to drop renamed table until you have committed the transaction without error and your row count after the insert matches the rowcount in the new table.
    Last edited by Thrasymachus; 03-30-06 at 07:03.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •