To prevent an X-Y problem here's the actual problem we're trying to solve:
We have a bunch of lookup tables that were unfortunately created with an identity column on the Primary Key, which is an
int. We wish we could simply remove the identity, however, we have some large tables with foreign keys pointing to the identity columns, and my understanding is removing the identity in this case is difficult. The reason we regret the identity is because these tables need to be synced across multiple environments, and developers insert data into these tables by writing scripts, and we run these scripts on multiple environments but not necessarily always in the same order, and so we ask developers to always:
- Enable Identity Insert
- Insert the row(s) with hard-coded integer IDs
- Disable Identity Insert
If everyone does that the data will either remain synced, or a script will fail and we can take immediate corrective action to resolve the conflict. But of course, sometimes the developers forget to follow the rules and just insert without the identity, and the auto increment of different scripts running in different orders in different environments causes them to get out of sync, and then problems arise.
Can we force the developers to always specify the identity column? I don't think there is a way to simply disable the Identity on these tables. What if we reseed the identity to a low number? When the seed value already exists, any insert that doesn't specify all columns will fail, and continue to fail until the number of insert attempts exceeds the number of existing (consecutive) rows. But after just one proper insert, that reseeds the table and the next improper insert will use the auto-increment again. So the extrapolation of this idea is to reseed the table to a low existing number after every insert (perhaps with a trigger, which feels odd, but might work?), or on a schedule, or perhaps every time we run the developers' scripts.
Is that a reasonable idea, and/or is there a better solution?
Side Note: we do have some other ideas, which I believe are out of scope for this question, for example:
- A gated-checkin that would parse the scripts for inserting into certain tables without specifying the identity column, and fail if we detect this.
- Store all of this data in source and update the entire table when deploying. (Rather than using run-once insert scripts.)
- Don't run data scripts that alter these tables on all envs, but use replication or another syncing mechanism.
Although these other ideas may be better in the long run, it seems like the lowest hanging fruit is just reseeding these tables so improper inserts will fail.