re-assigning mysql user id

Question

I'm not sure if it is possible but is there a way to re-assign an attribute 'id' in a database, Such as id=1,2,3,4,5,6 (6 elements), then id=3 is deleted, so when you look in the database your 'id' is re-assigned back in order like. id=1,2,3,4,5 (5 elements after id=3 is deleted). My 'id' attribute is auto_increment.

id    Name
1     A
2     B
3     C
4     D
5     E
6     F

then id=3 is deleted

id    Name
1     A
2     B<--   3 is deleted
4     D<--   name C is deleted
5     E
6     F

I then want to re-assign id back in order

id    Name
1     A
2     B
3     D
4     E
5     F

I can't find a way to get this done. I hope you can help.

_“I can't find a way to get this done.”_ – it __should not__ be done. An id has the one single purpose of identifying a record, nothing else. — CBroe, May 02 '14 at 03:04
I understand that every one advise against it but I was given a program and for some reason the program only works when the id is in order without any breaks starting at id=1,2,3,4,5,6 and so on. I've tried changing the program and I've gotten no where. I do know if I'm able to re-assign the id then my program would work. — Jordan, May 02 '14 at 03:18
possible duplicate of [get last auto increment php](http://stackoverflow.com/questions/23394546/get-last-auto-increment-php) — VBCPP, May 02 '14 at 04:17

score 1 · Answer 1 · edited May 23 '17 at 12:05

If using a surrogate KEY/PK, treat it as a surrogate key - that is, the value is "of no importance" other than it guaranteed to be unique per the relation domain.

That is, it is merely an implementation detail with no relation to data - trying to "compact" this sequence or otherwise use it for sequential ordering violates this.

To "compact" an auto_increment PK column, see my answer here (as compaction is required to "reset" the auto_increment seed). However, I advise against this because it violates a surrogate PK column, as per my previous comment.

Now, all the above aside, I believe one solution might be to introduce a sequence number in the queries that are otherwise "making the program break" - depending upon how the program is fed data, what it uses it for, and how it breaks, of course.

SET @i := 0;
SELECT
    (@i := (@i+1)) as seqNo
  , name
FROM people
ORDER BY id

This sequence number (seqNo) only relates to the row number in that result set and could otherwise be generated client-side as well - it is not related to the ID, although the ID is used to guarantee a stable ordering.

I understand that every one advise against it but I was given a program and for some reason the program only works when the id is in order without any breaks starting at id=1,2,3,4,5,6 and so on. I've tried changing the program itself to except any id number out of order and I've gotten no where. I do know if I'm able to re-assign the id then my program would work — Jordan, May 02 '14 at 03:19
@Jordan The *correct* solution is to fix the program (and find out why it breaks). In any case, my linked answer contains the solution for "compaction" - however, such will have to be done after *any* gap is introduced as is thus a *poor patch*, even as a temporary hack. — user2864740, May 02 '14 at 03:28

score 0 · Accepted Answer · answered May 02 '14 at 03:25

First think carefully if you really need this. In my practice I've never had to rebuild the order of a table with auto increment column.

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT `Name` FROM `table1`);
TRUNCATE `table1`;
INSERT INTO `table1` (`Name`) (SELECT `Name` FROM table2)

This won't work if you have any foreign keys in your table

re-assigning mysql user id

2 Answers2