Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Jul 27, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Adding the Identity Property to Existing Fields

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?

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 ), where this primary key is a foreign key in another table. Those constraints have to be dropped (and recreated) too.

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!

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date