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

Question

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:

1. Enable Identity Insert
2. Insert the row(s) with hard-coded integer IDs
3. 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 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:

1. A gated-checkin that would parse the scripts for inserting into certain tables without specifying the identity column, and fail if we detect this.
2. Store all of this data in source and update the entire table when deploying. (Rather than using run-once insert scripts.)
3. 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.

Answer 1

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'.

Answer 2

Triggered

Another alternative is to use an instead of trigger:

CREATE TABLE dbo.Regret 
(
    i integer IDENTITY(1, 1) NOT NULL 
        CONSTRAINT [PK dbo.Regret i] 
        PRIMARY KEY CLUSTERED,
    v integer NOT NULL
);

CREATE TABLE dbo.Consequence 
(
    ri integer NOT NULL
        CONSTRAINT [FK dbo.Regret i]
        FOREIGN KEY (ri)
        REFERENCES dbo.Regret (i)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    c integer NOT NULL
);

CREATE OR ALTER TRIGGER MustUseIdentityInsert
ON dbo.Regret
INSTEAD OF INSERT
AS
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT, XACT_ABORT ON;
SET ROWCOUNT 0;

INSERT dbo.Regret 
    (i, v)
SELECT 
    -- The identity column arrives containing zero if
    -- an explicit value wasn't provided by the original statement
    NULLIF(I.i, 0), I.v
FROM Inserted AS I;
GO

Tests:

--Msg 544, Level 16, State 1, Procedure MustUseIdentityInsert
--Cannot insert explicit value for identity column in table 'Regret' 
--when IDENTITY_INSERT is set to OFF.
INSERT dbo.Regret (v) 
VALUES (1);

--Msg 544, Level 16, State 1
--Cannot insert explicit value for identity column in table 'Regret' 
--when IDENTITY_INSERT is set to OFF.
INSERT dbo.Regret (i, v) 
VALUES (1, 1);

SET IDENTITY_INSERT dbo.Regret ON;

--Msg 515, Level 16, State 2, Procedure MustUseIdentityInsert
--Cannot insert the value NULL into column 'i', table 'Sandpit.dbo.Regret'; 
--column does not allow nulls. INSERT fails.
INSERT dbo.Regret (v) 
VALUES (1);

-- Success (with IDENTITY_INSERT still on)
INSERT dbo.Regret 
    (i, v) 
VALUES 
    (1, 1),
    (2, 2);

-- Also succeeds
MERGE dbo.Regret AS R
USING 
(
    SELECT SV.number
    FROM master.dbo.spt_values AS SV
    WHERE SV.[type] = N'P'
    AND SV.number > 0
) AS U (n)
    ON U.n = R.i
WHEN NOT MATCHED THEN
    INSERT (i, v) 
    VALUES (U.n, CHECKSUM(NEWID()));

SET IDENTITY_INSERT dbo.Regret OFF;

One drawback to this idea is you can't use an explicit identity value of zero.

I'd probably prefer one of the reseeding approaches but this is an alternative.

Removing identity

This is a pain and deserves direct language support.

It can be made less traumatic in some cases involving large tables using the well-known SWITCH procedure. This doesn't really apply to your case since the lookup table is small and the referencing tables are large. Importantly, recreating the foreign key will take a while and hold Sch-M locks, blocking all other activity on both tables.

Nevertheless:

IF COLUMNPROPERTY(OBJECT_ID(N'dbo.Regret', 'U'), N'i', 'IsIdentity') = 1
BEGIN TRY
    BEGIN TRANSACTION;

        DROP TABLE IF EXISTS dbo.NoRegret;

        -- Switch target with a compatible schema but no IDENTITY
        CREATE TABLE dbo.NoRegret 
        (
            i integer NOT NULL 
                CONSTRAINT [PK dbo.NoRegret i] 
                PRIMARY KEY,
            v integer NOT NULL
        );

        -- Drop the FK
        ALTER TABLE dbo.Consequence 
            DROP CONSTRAINT [FK dbo.Regret i];

        -- Metadata-only switch
        ALTER TABLE dbo.Regret 
            SWITCH TO dbo.NoRegret;

        -- Don't need the original now
        DROP TABLE dbo.Regret;

        -- Fix up the names
        EXECUTE sys.sp_rename
            @objname = N'dbo.NoRegret',
            @newname = N'Regret',
            @objtype = 'OBJECT';

        EXECUTE sys.sp_rename
            @objname = N'[PK dbo.NoRegret i]',
            @newname = N'[PK dbo.Regret i]',
            @objtype = 'OBJECT';

        -- Add the FK back
        ALTER TABLE dbo.Consequence 
            WITH CHECK ADD
                CONSTRAINT [FK dbo.Regret i]
                FOREIGN KEY (ri) 
                REFERENCES dbo.Regret;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Abstraction

Addressing one final point, it is heaps easier to change things when there's a little redirection involved. In your situation, the mechanics of deployment are tightly coupled to the database physical design (the identity property).

It would've been easier to make this change if there were no direct data access to tables, with all data changes made through stored procedures. Adding checks or changes to stored procedure code is usually trivial.

In an ideal world, deployment scripts in general should be capable of being run (and re-run) against any current state, taking the code and database to the same end state. No doubt you're aware of all this.

Answer 3

it seems like the lowest hanging fruit is just reseeding these tables so improper inserts will fail

I wouldn't say that's low hanging fruit because...

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.

...which just sounds like a crazy train, but the bad kind without Ozzy.

I'd recommend either:

1. Fixing the process, and training certain developers who are more apt on the database side to be the central source of manual scripting and deployment (when identity columns are involved at least).

2. Using a data synchronization tool like SQL Data Examiner to easily generate the scripts automatically.

In either case, I'd recommend that you test deploying your scripts to a Staging type of environment as opposed to going directly into Production. That way you have the time and flexibility to correct them when mistakes do happen, which is typically more than just identity value issues when manual scripting is involved.