renumber primary key

Question

How would I reset the primary key counter on a sql table and update each row with a new primary key?

Answer 1

I would add another column to the table first, populate that with the new PK.

Then I'd use update statements to update the new fk fields in all related tables.

Then you can drop the old PK and old fk fields.

EDIT: Yes, as Ian says you will have to drop and then recreate all foreign key constraints.

Answer 2

This may or not be MS SQL specific, but: TRUNCATE TABLE resets the identity counter, so one way to do this quick and dirty would be to 1) Do a Backup 2) Copy table contents to temp table: 3) Copy temp table contents back to table (which has the identity column):

SELECT Field1, Field2 INTO #MyTable FROM MyTable

TRUNCATE TABLE MyTable

INSERT INTO MyTable
(Field1, Field2)
SELECT Field1, Field2 FROM #MyTable

SELECT * FROM MyTable
-----------------------------------
ID    Field1    Field2
1     Value1    Value2

Answer 3

Not sure which DBMS you're using but if it happens to be SQL Server:

SET IDENTITY INSERT [MyTable] ON

allows you to update/insert the primary key column. Then when you are done updating the keys (you could use a CURSOR for this if the logic is complicated)

SET IDENTITY INSERT [MyTable] OFF

Hope that helps!

Answer 4

Why would you even bother? The whole point of counter-based "identity" primary keys is that the numbers are arbitrary and meaningless.

Answer 5

you could do it in the following steps.

• add new_key field to the table
• populate new_key with the desired values
• temporarily disable constraints
• update all related tables to point to the value of new_key instead of the old_key
• SET IDENTITY INSERT [MyTable] ON
• update old_key with the values of new_key
• SET IDENTITY INSERT [MyTable] OFF
• reseed identity
• drop new_key field
• re-enable constraints

But as others said all that work is not needed. I tend to look at the identity type primary keys as if they were equivalent of pointers in C, I use them to reference other objects but never modify of access them explicitly

Answer 6

If this is Microsoft's SQL Server, one thing you could do is use the dbcc checkident

Assume you have a single table that you want to move around data within along with renumbering the primary keys. For the example, the name of the table is ErrorCode. It has two fields, ErrorCodeID (which is the primary key) and a Description.

Example Code Using dbcc checkident

-- Reset the primary key counter
dbcc checkident(ErrorCode, reseed, 7000)

-- Move all rows greater than 8000 to the 7000 range
insert into ErrorCode
select Description from ErrorCode where ErrorCodeID >= 8000

-- Delete the old rows
delete ErrorCode where ErrorCodeID >= 8000

-- Reset the primary key counter
dbcc checkident(ErrorCode, reseed, 8000)

With this example, you'll effectively be moving all rows to a different primary key and then resetting so the next insert takes on an 8000 ID.

Hope this helps a bit!