Adding an identity to an existing column

Question

Adding an identity to an existing column

I want to modify the primary key of a table to an identity column, and there's previously a number of rows in the table.

I've got a script to clean up the IDs to ensure they're regular starting at 1, works fine on my test database.

What's the SQL command to alter the column to have an identity property?

sql-server sql

4 years ago by Amit8z4mc

2 Answers

Login / Signup to Answer the Question.

Login Signup

**espadacoder11** · Answer 1 · 2021-06-08T12:23:20

You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.



ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)



ALTER TABLE (yourTable) DROP COLUMN OldColumnName



EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

**kshitijrana14** · Answer 2 · 2021-06-10T04:43:30

Right click on table name in Object Explorer. You will get some options. Click on 'Design'. A new tab will be opened for this table. You can add Identity constraint here in 'Column Properties'.

C TUTORIAL

C PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

C++ TUTORIAL

C++ PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

PYTHON TUTORIAL

PYTHON HOW TOS

INTERVIEW TESTS

EXECUTE CODE

JAVA TUTORIAL

JAVA CODE EXAMPLES

SPRING TUTORIAL

MORE IN JAVA

COMPUTER ARCHITECTURE

COMPUTER NETWORK

OPERATING SYSTEM

DBMS & SQL

PL/SQL

MongoDB

EXECUTE SQL

ANDROID DEVELOPMENT

GO LANGUAGE

LINUX

DOCKER

HTML TAGS (A to Z)

CSS REFERENCES

SASS/SCSS

KOTLIN

GAME DEVELOPMENT

PHP

GIT GUIDE

JAVASCRIPT

ADVANCED DSA

Adding an identity to an existing column

2 Answers

Login / Signup to Answer the Question.