Add Indentity (auto-increment) to primary key of existing table [duplicate]

This question already has answers here: Closed 11开发者_开发知识库 years ago.

Possible Duplicate:

Adding an identity to an existing column

how to set auto increment after creating a table without any data loss?

I have a table Activities with two columns: Activity and ActivityId. ActivityId is the primary key. This table is referenced by a Users Table, which has an ActivityId column (this column is not marked as a foreign key - is this a problem?)

When this table was created, I thought it was going to be pretty static, but now I find I need to be able to add and remove rows from it fairly often, so I want to make the ActivityId column auto increment when rows are added to it. I've read that in sql server, I do this by making the column the Identity column, but that this only works for new tables.

How do I add an identity to an existing table, while keeping the database consistant? For instance, it's ok if the IDs are renumbered, so long as this change propagates through to the user table.

I tried to add the identity in SQL Server Management Studio, but it wouldn't let me save the changes due to needing to drop and recreate the table.

---

You need to either:

• Add a NEW column that has the identity property, and alter your PK to include that field (and I highly recommend you do an ALTER TABLE REBUILD afterwards to clean up all the pointers you will create from page splits)

• Create a new table with the desired structure and put all your existing data into it

---

If you are on Enterprise / Developer edition see my answer here otherwise it is not possible without rebuilding the table.

Enabling this in SSMS is just an option you can set then it will generate the whole script for you that preserves existing identity values (Tools -> Options -> Designers -> Untick "prevent saving changes that require table re-creation")