How reorder primary key?

Question

Hi; I have deleted one row(row 20) in my "table category" ,please let me know that how can i reorder the catid (primary key)? at this time it is 21 after 19.

Thanks

Answer 1

What do you mean by reordering primary key? If you are saying that you want the primary key to take 20 instead of 21, then I afraid you can't do that straightaway.

All you can do, is to drop the primary key constraint, then change the 21 to 20, and reapply back the primary key constraint

Answer 2

You cannot. The closest you can get is truncate table, which will drop the table and recreate it, which means you lose all data in it, and the ID counter is reset to 0. Other than that, ID will always increment by one from the last inserted record, no matter if that record still exists or not. You can write a query to fix all your current IDs, of course, but upon next insert, it'll still create a new gap. More to the point: if a sequential ordering without gaps is what you want, auto incremental ID is not the proper way to achieve that. Add another int field where you manually keep track of this ordering.

Answer 3

Don't mess with the primary keys. They should never change and you should not use them in your app for anything but joining tables.

Add a new column if you need a gapless index and update this column accordingly when you do inserts/removes. This might sound like useless work for you right now, but it will save you a lot of pain later.

Answer 4

If you care enough about your primary key values that such a value is unwanted, you shouldn't be using auto-number primary keys in the first place.

The whole point with a auto-number key is that you say "As long as the key is unique, I don't really care about its value."

Answer 5

David is right about not using primary key for indexing and such.

If you'll just have to change a particular primary key value once (I've done it sometimes during migration) you could of course set identity_insert on and copy the row with a insert select and then delete the original one.

For recreating a sort order or an column used as an index in your application you could use the following stored procedure:

CREATE PROCEDURE [dbo].[OrganizeOrderConfirmationMessages] 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sortOrder INT;
    SET @sortOrder = 0;

    -- // Create temporary table
    CREATE TABLE #IDs(ID INT, SortOrder INT)

    -- // Insert IDs in order according to current SortOrder
    INSERT INTO #IDs SELECT ocm.ID, 0 FROM OrderConfirmationMessages ocm ORDER BY ocm.SortOrder ASC

    -- // Update SortOrders
    UPDATE #IDs SET SortOrder = @sortOrder, @sortOrder = @sortOrder + 10

    -- // Update the "real" values with data from #IDs
    UPDATE OrderConfirmationMessages SET SortOrder = x2.SortOrder
    FROM #IDs x2  WHERE OrderConfirmationMessages.ID = x2.ID    

END

Results:

An example of SortOrders will go from 1,2,5,7,10,24,36 to 10,20,30,40,50,60,70