CODEWITHSUNDEEP
sqlprimary-key
Aaron Fischer
Aaron Fischer

Reputation: 21211

renumber primary key

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

Upvotes: 8

Views: 17180

Answers (7)

Shaji Joseph
Shaji Joseph

Reputation: 11

  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.

Upvotes: 0

Zachary Yates
Zachary Yates

Reputation: 13386

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!

Upvotes: 6

kristof
kristof

Reputation: 53834

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

Upvotes: 1

Scott Saad
Scott Saad

Reputation: 18372

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!

Upvotes: -1

Tony Andrews
Tony Andrews

Reputation: 132580

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

Upvotes: 1

Codewerks
Codewerks

Reputation: 5974

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

Upvotes: 3

Galwegian
Galwegian

Reputation: 42237

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.

Upvotes: 15

Related Questions