Adding the Identity Property to Existing Fields

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&#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?

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 ), 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!

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

Top 5 B2B SaaS Marketing Agencies for 2023

In recent years, the software-as-a-service (SaaS) sector has experienced exponential growth as more and more companies choose cloud-based solutions. Any SaaS company hoping to stay ahead of the curve in this quickly changing industry needs to invest in effective marketing. So selecting the best marketing agency can mean the difference

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.