8

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

Guido
  • 46,642
  • 28
  • 120
  • 174
Aaron Fischer
  • 20,853
  • 18
  • 75
  • 116
  • 3
    Primary keys shouldn't need renumbered... because the number is irrelevant. 1 is as good as 32 is as good as 212479817423. You've built a smart key otherwise. –  Oct 23 '08 at 14:59
  • Remember to address the issue of broken foreign keys. – DOK Oct 23 '08 at 17:01
  • 2
    I need to do a renumber because int can only get so large and I am almost at its limit. there was lots of fragmentation in the key and so renumber would give me a lot more room... – djangofan Oct 24 '11 at 18:29
  • 3
    @Mark Brady, not a very useful comment. There are many legit reasons to want to renumber ids. One is to remove fragmentation, for instance when you're running out of space. – md1337 Feb 20 '13 at 16:17

7 Answers7

15

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.

Galwegian
  • 41,475
  • 16
  • 112
  • 158
  • Agreed. You would also need to drop any FK constraints before doing the updates, and reapply them afterwards. – Ian Nelson Oct 23 '08 at 14:48
  • 1
    Instead of dropping and creating constraints you can disable, and then enable them again once you finish. It is only 1 line of sql code see here http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-tsql#161410 – kristof Jan 21 '09 at 15:48
6

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!

Sk93
  • 3,676
  • 3
  • 37
  • 67
Zachary Yates
  • 12,966
  • 7
  • 55
  • 87
3

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
Codewerks
  • 5,884
  • 5
  • 29
  • 33
1

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

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 2
    Seriously, it anyones me. I have a couple of tables that hold data. This data changes during the development process. Before I ship, I would like the primary key to start with 1 and be consecutive. I don't want the customer to see I changed things. – Aaron Fischer Oct 23 '08 at 15:37
  • Couldn't you just drop and recreate the tables since you probably want to blank them out before you ship? – JohnFx Oct 23 '08 at 16:31
  • 9
    Why do you care why he wants to do it? He might have a perfectly sensible requirement. – Iain Holder Nov 30 '08 at 11:26
  • Or to hide poor DBA practices, such as deleting records? Hmmm? – Fandango68 Jul 16 '19 at 05:09
1

you could do it in the following steps:

  • create copy of yourTable with extra column new_key
  • populate copyOfYourTable with the affected rows from yourTable along with desired values of new_key
  • temporarily disable constraints
  • update all related tables to point to the value of new_key instead of the old_key
  • delete affected rows from yourTable
  • SET IDENTITY_INSERT [yourTable] ON
  • insert affected rows again with the new proper value of the key (from copy table)
  • SET IDENTITY_INSERT [yourTable] OFF
  • reseed identity
  • re-enable constraints
  • delete the copyOfYourtable

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

Community
  • 1
  • 1
kristof
  • 52,923
  • 24
  • 87
  • 110
  • Unfortunately the part about "update old_key with the values of new_key" is wrong, you can't update an identity column. You can only insert into it. And the syntax for SET IDENTITY is wrong, it's SET IDENTITY_INSERT (underscore). – md1337 Feb 20 '13 at 21:14
0
  1. Export the table to sql file.
  2. Then copy the insert query from the *.sql file and make all the Autoincrement field to 'NULL'.
  3. Truncate the table, then run the modified query.
Shaji Joseph
  • 11
  • 1
-1

If this is Microsoft's SQL Server, one thing you could do is use the [dbcc checkident](http://msdn.microsoft.com/en-us/library/ms176057(SQL.90).aspx)

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!

Scott Saad
  • 17,962
  • 11
  • 63
  • 84