0

I have an existing table where I use existing column (type INT) as PK and manually increment its value with each row inserted. I wanted to change it to IDENTITY with auto increment. I found a thread here (http://stackoverflow.com/questions/4862385/sql-server-add-auto-increment-primary-key-to-existing-table) that seems to achieve exactly what I want. But every time I run the ALTER statement, Mgmt Studio crashes.

I had also tried to achieve my above goal by changing the column properties manually (Identity specification/Is Identity:yes) as in this thread (http://stackoverflow.com/questions/3876785/sql-server-cant-insert-null-into-primary-key-field). But every time I close the table after changing properties, I get an error

'Pix' table
Unable to modify table.
Cannot insert the value NULL into column 'picID', table 'photo.dbo.Tmp_Pix'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Not sure what's going on.

Khaled
  • 9
  • 1
  • 4

1 Answers1

1

You cannot change an existing column to become an IDENTITY column.

What you need to do is:

  • create a new column with INT IDENTITY
  • drop the primary key constraint
  • drop the old column
  • add the primary key constraint on the new column

The trouble might be - if you already have data in that table - that the new identity values don't necessarily match the old values in your manual ID column.

If you need to preserve those, then it gets even more involved:

  • create a new table with the proper structure, and make sure that the ID column is INT IDENTITY
  • turn on IDENTITY_INSERT for that table
  • insert all the rows from the old table into the new one (and in the process, insert the old ID values into the new ID IDENTITY column)
  • turn off IDENTITY_INSERT for that table
  • drop the old table
  • possibly rename the new table
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459