Adding the Identity Property to Existing Fields

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&#151that 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.

See also  Comparing different methods of testing your Infrastructure-as-Code

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!


About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist