Question:
I have a users table with primary key USERID. The table is already created and loaded with pre-existing data. I now want the USERID field to be self-incrementing—that is, using the identity property. Can I alter a column in an ID table once the table and column are created or do I need to recreate it and reload all of the data?
Answer:
Adding the column is easy, but you will still have some issues dealing with constraints and the primary key.
An altered column cannot be a primary key, so you will need to drop that constraint first (and recreate it when you’re done). You need at least this:
Alter Table employee Drop Constraint ck_emp_id
Be aware that this change may have implications for your other tables (really big implications
Once the constraints are dropped, you could add an identity column to your table like this:
Alter Table mytable Add newkey Int Identity(1,1)
This creates the new column for you and populates it with integers. Then you could rename the column to the old column name, recreate the constraints and relationships, and be on your way.
There’s another restriction you should know about. You may only have one identity column per table. Additionally, there have been incidents where the identity property became out of synch, leaving gaps in its sequence.
There is another technique to accomplish sequential integer or character IDs. It rests on using an insert trigger and an ID table. The ID table holds the table names and their last highest primary key value used. After every insertion into the named table, an insert trigger generates the next highest ID for that table. Then you can use that ID for your insertion.
You won’t be able to alter the column directly, or else you’ll get data-type mismatches between what’s in the column now and the integer you want there. You’ll need to massage the data somewhat (preserving the old values so you can backtrack into the relationships).
Good luck!
