How do I add Auto Increment to Existing SQL ID Primary Key?

Question

I have an empty table that I thought was auto increment until I tried adding my first record. That's when SQL told me this:

Cannot insert the value NULL into column 'ID', table 'DB_9CF886_mcl959.dbo.Posts'; column does not allow nulls. INSERT fails.

So I tried to add the identity to it:

alter table dbo.Posts ADD ID INT IDENTITY(1,1) CONSTRAINT PK_Posts1 PRIMARY KEY CLUSTERED;

That gave me this error:

Column names in each table must be unique. Column name 'ID' in table 'dbo.Posts' is specified more than once.

I could drop the table and recreate it, but I'd like to know how to FIX IT to add the identity to the ID column.

Screenshot of database: dbo.Posts

You should always make your columns `NOT NULL` unless you have a good reason otherwise, btw. — Dai, Jun 21 '23 at 22:07
@Dai - I think so. Basically, I cannot use the current `ID` column then. Right? — podrick, Jun 21 '23 at 22:26
Well, this is embarrassing - I could swear that `IDENTITY` could be added to an existing column but I guess not - HOWEVER, you _can_ still use a `SEQUENCE` with an existing column - which will achieve the same effect. — Dai, Jun 21 '23 at 22:30
I've just posted my answer that demonstrates how to use a `SEQUENCE` object to make an existing column behave like an `IDENTITY` column without dropping any objects or losing any data or breaking any referential-integrity. — Dai, Jun 21 '23 at 22:38

Dai · Accepted Answer · 2023-06-21T22:39:32.820

While it's true that you cannot add IDENTITY to an existing column, and that adding IDENTITY to an existing table means adding a new column - it's also true that you don't need to use IDENTITY - nor do you need to DROP anything either.

Instead, ALTER your existing ID column to use a SEQUENCE object for new values - which has (effectively) the same end-result as IDENTITY - but this way it will preserve your existing ID column values, preserve foreign-key references, and preserve your CLUSTERED index without needing a table rebuild.

Do this:

SET XACT_ABORT ON;

BEGIN TRANSACTION;

DECLARE @baseId int = ( SELECT ISNULL( MAX( ID ), 0 ) FROM dbo.Posts ) + 1;

-- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql
CREATE SEQUENCE dbo.PostsIdSequence START WITH @baseId INCREMENT BY 1;

ALTER TABLE dbo.Posts
    ADD CONSTRAINT DF_Posts_Sequence DEFAULT NEXT VALUE FOR dbo.PostsIdSequence FOR "ID";

COMMIT TRANSACTION;

score 3 · Answer 2 · answered Jun 21 '23 at 22:11

Below is an example that uses SWITCH to move data into another table of identical schema that already has the IDENTITY.

--create sample table and data
CREATE TABLE dbo.AddIdentityExample(
      IncrementalColumn int NOT NULL
        CONSTRAINT PK_AddIdentityExample PRIMARY KEY
    , OtherData varchar(20) NULL
    );
CREATE INDEX idx_AddIdentityExample_OtherData
    ON dbo.AddIdentityExample(OtherData);
INSERT INTO dbo.AddIdentityExample VALUES
      (1, 'Sample data one')
    , (2, 'Sample data two')
    , (3, 'Sample data three');
GO

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    --create staging table with same schema, indexes, and constraints
    CREATE TABLE dbo.AddIdentityExampleStaging(
          IncrementalColumn int IDENTITY NOT NULL --IDENTITY column property added
            CONSTRAINT PK_AddIdentityExampleStaging PRIMARY KEY
        , OtherData varchar(20) NULL
        );
    CREATE INDEX idx_AddIdentityExampleStaging_OtherData
        ON dbo.AddIdentityExampleStaging(OtherData);

    --move data from source table into new table
    ALTER TABLE dbo.AddIdentityExample
        SWITCH TO dbo.AddIdentityExampleStaging;
    DROP TABLE dbo.AddIdentityExample;
    --rename table
    EXEC sp_rename
          @objname = N'dbo.AddIdentityExampleStaging'
        , @newname = N'AddIdentityExample'
        , @objtype = 'OBJECT';
    --rename constraints
    EXEC sp_rename
          @objname = N'dbo.PK_AddIdentityExampleStaging'
        , @newname = N'PK_AddIdentityExample'
        , @objtype = 'OBJECT';
    --rename indexes
    EXEC sp_rename
          @objname = N'dbo.AddIdentityExample.idx_AddIdentityExampleStaging_OtherData'
        , @newname = N'idx_AddIdentityExampleStaging_OtherData'
        , @objtype = N'INDEX';
    --seed IDENTITY with current max column value
    DBCC CHECKIDENT(N'dbo.AddIdentityExample');
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO

Great answer. `BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK;` is not necessary if you have `XACT_ABORT ON` — Charlieface, Jun 22 '23 at 00:22

How do I add Auto Increment to Existing SQL ID Primary Key?

2 Answers2