Is there a smart way to append a number to an PK identity column in a Relational database w/o total catastrophe?

It's far from the ideal situation, but I need to fix a database by appending the number "1" to the PK Identiy column which has FK relations to four other tables. I'm basically making a four digit number a five digit number. I need to maintain the relations. I could store the number in a var, do a Set query and append the 1, and do that 开发者_如何学JAVAfor each table...

Is there a better way of doing this?

You say you are using an identity data type for your primary key so before you update the numbers you will have to SET IDENTITY_INSERT ON (documentation here) and then turn it off again after the update.

As long as you have cascading updates set for your relations the other tables should be updated automatically.

EDIT: As it's not possible to change an identity value I guess you have to export the data, set the new identity values (+10000) and then import your data again.

Anyone have a better suggestion...

Consider adding another field to the PK instead of extending the length of the PK field. Your new field will have to cascade to the related tables, like a field length increase would, but you get to retain your original PK values.

My suggestion is:

1. Stop writing to the tables.
2. Copy the tables to new tables with the new PK.
3. Rename the old tables to backup names.
4. Rename the new tables to the original table name.
5. Count the rows in all the tables and double check your work.
6. Continue using the tables.

Changing a PK after the fact is not fun.

If the column in question has an identity property on it, it gets complicated. This is more-or-less how I'd do it:

• Back up your database.

• Put it in single user mode. You don't need anybody mucking around whilst you do the surgery.

• Execute the ALTER TABLE statements necessary to

  • disable the primary key constraint on the table in question
  • disable all triggers on the table in question
  • disable all foreign key constraints referencing the table in question.

• Clone your table, giving it a new name and a column-for-column identical definitions. Don't bother with any triggers, indices, foreign keys or other constraints. Omit the identity property from the table's definition.

• Create a new 'map' table that will map your old id values to the new value:

  create table dbo.pk_map
  (
    old_id int not null primary key clustered ,
    new_id int not null unique nonclustered ,
  )
  

• Populate the map table:

  insert dbo.pk_map
  select old_id = old.id ,
         new_id = f( old.id ) // f(x) is the desired transform
  from dbo.tableInQuestion old
  

• Populate your new table, giving the primary key column the new value:

  insert dbo.tableInQuestion_NEW
  select id = map.id ,
         ...
  from dbo.tableInQuestion old
  join dbo.pk_map          map on map.old_id = old.id
  

• Truncate the original table: TRUNCATE dbo.tableInQuestion. This should work—safely—since you've disabled all the triggers and foreign key constraints.

• Execute SET IDENTITY_INSERT dbo.tableInQuestion ON.

• Reload the original table:

  insert dbo.tableInQuestion
  select *
  from dbo.tableInQuestion_NEW
  

• Execute SET IDENTITY_INSERT dbo.tableInQuestion OFF

• Execute drop table dbo.tableInQuestion_NEW. We're all done with it.

• Execute DBCC CHECKIDENT( dbo.tableInQuestion , reseed ) to get the identity counter back in sync with the data in the table.

• Now, use the map table to propagate the changed primary key column down the line. Depending on your E-R model, this can get complicated as foreign keys referencing the updated column may themselves be part of a composite primary key.

• When you're all done, start re-enabling the constraints and triggers you disabled. Make sure you do this using the WITH CHECK option. Fix any problems thus uncovered.

• Finally, drop the map table, and clear the single user flag and bring your system(s) back online.

Piece of cake! (or something.)

Consider this approach: Reset the identity seed to the 10000 + the current seed. Set identity insert on Insert into the table from the values in the table and add 10000 to the identity column on the way. EX:

Set identity insert on
Insert Table(identity, column1, eolumn2)
select identity + 10000, column1, column2
From Table
Where identity < origional max identity value

After the insert you know the identity is exactly 10000 more than the origional. Update the foreign keys by addding 10000.