Regretting an identity: Is there a way to force inserts to specify the identity column?

To prevent an X-Y problem here's the actual problem we're trying to solve:

The Problem:

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.

One Idea:

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 t

Instead of "reseeding the table to a low existing number" and then having to reset the seed after every insert that does it "properly" a better idea (assuming a positive increment value as is usual) would likely be to set it to the max value supported by the datatype.

This should leave auto generation of IDENTITY values broken unless someone reseeds it again.

Example (Errors returned are "Arithmetic overflow error converting IDENTITY to data type int." in both catch blocks)

DROP TABLE IF EXISTS dbo.MyLookupTable

CREATE TABLE dbo.MyLookupTable
(
IdentityColumnOfWoe INT IDENTITY PRIMARY KEY,
SomeValue VARCHAR(30)
)

SET IDENTITY_INSERT dbo.MyLookupTable ON

INSERT dbo.MyLookupTable(IdentityColumnOfWoe, SomeValue)
VALUES (1, 'Explicit value 1'),
       (2, 'Explicit value 2');

SET IDENTITY_INSERT dbo.MyLookupTable OFF

DBCC CHECKIDENT ('dbo.MyLookupTable', RESEED, 2147483647);

BEGIN TRY
INSERT dbo.MyLookupTable VALUES ('Bad Insert')
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH

SET IDENTITY_INSERT dbo.MyLookupTable ON

INSERT dbo.MyLookupTable(IdentityColumnOfWoe, SomeValue)
VALUES (3, 'Explicit value 3');

SET IDENTITY_INSERT dbo.MyLookupTable OFF

BEGIN TRY
INSERT dbo.MyLookupTable VALUES ('Still broken?')
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH

A variant on this (dependent on how much spare capacity the datatype gives) could be to reserve a space for bad inserts and block them with a check constraint. This could give a more informative error message.

ALTER TABLE dbo.MyLookupTable ADD CONSTRAINT CK_MyLookupTable_AlwaysUseSetIdentityInsert CHECK (IdentityColumnOfWoe <= 2000000000)
DBCC CHECKIDENT ('dbo.MyLookupTable', RESEED, 2000000000);

Msg 547, Level 16, State 0, Line 21 The INSERT statement conflicted
with the CHECK constraint
"CK_MyLookupTable_AlwaysUseSetIdentityInsert". The conflict occurred
in database "Test", table "dbo.MyLookupTable", column
'IdentityColumnOfWoe'.

StackExchange Database Administrators Q#325151, answer score: 7