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")
精彩评论