How reorder primary key?

I have deleted one row(row 20) in my "table category" ,please let me know that how c开发者_如何学编程an i reorder the catid (primary key)? at this time it is 21 after 19.

Thanks

---

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.

---

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."

---

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.

---

Try this:

UPDATE tbl SET catid = (SELECT COUNT(*) FROM tbl t WHERE t.catid <= tbl.catid);

You might also want to rethink / redesign. Renumbering the entire table when you delete a row doesn't seem likely to be either practical or necessary.

---

Actually you can.

If your rows have unique enough data and you are using PHPmyAdmin

1. Delete the Column with the Primary ID
2. Read the Column with Primary Key and Auto Increment enabled.

---

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

---

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

---

You should drop the 'catid' field and then create it again, set it as primary and check the Auto Increment checkbox, it will add the new field and fill the numbers.

---

First drop the primary key column from your table and run this syntax in your phpmyadmin sql section-

ALTER TABLE 'your_tablename' ADD 'column_name' BIGINT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY
('column_name' (10));

This will automatically arrange the column in numbers from 0, 1 and so on.

---

try this:

SET @var:=0;
UPDATE `table` SET `id`=(@var:=@var+1);
ALTER TABLE `table` AUTO_INCREMENT=1; 

---

In postgres, you can do this where number of records < 300:

update schema.tbl1
set tbl_id = tbl_id + 300;

alter sequence schema.tbl1_id_seq
restart with 1;

insert into schema.tbl1
select nextval('schema.tbl1_id_seq'),
column2,
column3
from schema.tbl1;

delete from schema.tbl1
where tbl1_id > 300;