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

June 22, 2023

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

>Solution :

Run this approach which consist in creating a new column and dropping the old one.

UPDATE (thanks to user @dai)

BEGIN TRANSACTION;

Alter Table Posts Add Id_new Int Identity(1,1);

UPDATE Posts SET Id_new =id;

Alter Table Posts DROP CONSTRAINT PK_posts;--YOUR Current PK constraint name

ALTER TABLE Posts DROP COLUMN id;--Your Current PK column name

ALTER TABLE Posts ADD id int IDENTITY(1, 1) NOT NULL;

ALTER TABLE Posts ADD CONSTRAINT PK_posts PRIMARY KEY CLUSTERED (id);

SET IDENTITY_INSERT Posts OFF;

COMMIT TRANSACTION;