is it possible to update the autoincrement primary key values to the vacant values for mysql table

Question

This is my table with id field as unsigned tinyint PRIMARY KEY with NOT NULL and AUTO_INCREMENT.

+----+
| id |
+----+
|  1 |
|  4 |
|254 |
|255 |
+----+

My application is an embedded application so I am using the small datatypes like tinyint. My total count of id values wont go upto 255. But in case if the in between values are deleted for number of times then inserting the new id value will definitely reach 255 sooner or later.

I have some questions,

Is it possible to set the auto increment feature such that it will be insert the new id to the not existing greater value (here 2)?

If not please suggest some way to handle this issue other than using higher data type for id and updating the higher id values after deleting an id (like if I delete id = 2 update all the id values greater than 2 to id-1 so that all the values remain in sequence while inserting new value).

I won't question the need to keep a tinyint in your case but I want to point out that the auto-increment does not support a 'reset' feature, and anything you do will propably be custom to your needs. You may want to check [this](http://stackoverflow.com/questions/2214141/auto-increment-after-delete-in-mysql) SO q-and-a. — Sevle, Feb 26 '16 at 13:36
If you want to do something like this, then you definitely do not want the auto-incrementing feature. That said, I can think of no earthly reason why you would want to do something like this, and it almost certainly points to a flaw in your understanding of relational data. — Strawberry, Feb 26 '16 at 13:47
Im doing this mysql handling in C because my application is in C. — A J Brockberg, Feb 29 '16 at 14:31

Bernd Buffen · Answer 1 · 2016-02-26T16:36:43.553

1

Append your fields for insert. this will use the lowest free id

INSERT INTO ai (id) (
  SELECT a.id+1 FROM ai a
  LEFT JOIN ai b ON a.id+1 = b.id
  WHERE b.id IS NULL LIMIT 1
)

OR to get also 1

   SELECT a.id+1 FROM (
       SELECT 0 AS id UNION SELECT id FROM ai 
       ) AS a
   LEFT JOIN ai b ON a.id+1 = b.id
   WHERE b.id IS NULL
   ORDER by a.id ASC 
   LIMIT 1;

edited Feb 26 '16 at 16:36

answered Feb 26 '16 at 14:10

Bernd Buffen

14,525
2
24
39

Hi Bernd Buffen great it is working. But should I disable then the AUTO INCREMENT? – A J Brockberg Feb 26 '16 at 15:18
No thats not necessary. And its better to have an Autoincrement, so you can use both. Tha autoincrement was alway set to max+1 . so if you have only 3 rows in the table (id 1,2,3) and inserts id 100 auto_increment was set to 101. But they never sets to a lower vaue – Bernd Buffen Feb 26 '16 at 15:39
i have add it in my answer – Bernd Buffen Feb 26 '16 at 16:37
Hi Bernd Buffen the edited answer seems to be working perfect. But in this case I am unable to find the last inserted row. – A J Brockberg Feb 29 '16 at 06:46
What if there are no missing values 1-255 ? ;-) – Strawberry Feb 29 '16 at 11:43
My count of ids wont be going to more than 255 so I defined the uint8_t data type for the id. If I dont put on values which are deleted I might exceed with the id value greater than 255; in that case uint8_t data type will fail. – A J Brockberg Feb 29 '16 at 12:24

score 0 · Answer 2 · answered Feb 26 '16 at 15:22

0

I can't think why you choose to be bound by this arbitrary limit. That said, if were to do this then I would construct a table with all possible integers (1-255) and a flag indicating whether the value was currently in use. So a DELETE becomes UPDATE x SET flag = 0 WHERE id = n. Then the query to find the lowest 0 flag becomes trivial.

answered Feb 26 '16 at 15:22

Strawberry

33,750
13
40
57

thanks for the reply. Thats a really good idea. Since I am a newbie and I already said that I am working on an embedded application. I have another question that is it good to add another column instead of working like Bernd Buffen suggested? – A J Brockberg Feb 26 '16 at 15:28
I don't understand that solution, so I can't comment. – Strawberry Feb 26 '16 at 15:39
what if this primary key is linked to other table's foreign key? In that case setting flag = 0 wont delete the row in this parent table. But I might want to delete the rows from child table (I wont be able to use on delete cascade feature in that case so I have to explicitly delete those rows from all those child table) – A J Brockberg Feb 29 '16 at 07:55
Yes. I guess that's true. – Strawberry Feb 29 '16 at 09:48
In this case will it be a good way if I wont use auto increment and generating a random unique value between some range (like 1 - 255 ) as PRIMARY KEY? – A J Brockberg Feb 29 '16 at 11:38
Since it would be hectic to delete the id from all the child tables if I set its corresponding flag = 0 in the parent table (if I wont delete I have to maintain those unnecessary values and rows in all the child tables for that id). So in this case will it be a good way not to use flag and auto increment field? And instead to generate a random unique value as id (between 1 - 255). – A J Brockberg Feb 29 '16 at 11:58
The overhead of the additional delete might be annoying, but I still think it's the solution I'd adopt. I'd probably place the two queries inside a transaction so that the delete didn't get forgotten. What happens when all the values are taken? – Strawberry Feb 29 '16 at 12:07
I am sure that my total count values wont go above 255. – A J Brockberg Feb 29 '16 at 12:43

score 0 · Answer 3 · answered Feb 29 '16 at 12:29

0

@

Edge Goldberg - if you want to reorder the entrys and use auto_increment use this. Then also lst_insertid will work -- : -- After DELETE a ROW UPDATE abc , (SELECT @nr:=0) AS INIT SET a := @nr := (@nr+1); ALTER TABLE abc AUTO_INCREMENT=1;

-- INSERT a new one INSERT INTO abc (a) VALUES(1234); SELECT LAST_INSERT_ID();

answered Feb 29 '16 at 12:29

Bernd Buffen

14,525
2
24
39

i have add a answer, sorry, but i cant format it in comment – Bernd Buffen Feb 29 '16 at 13:44

score 0 · Answer 4 · answered Feb 29 '16 at 13:43

Here is the Sample for set auto_increment

MariaDB []> select * from abc;
+-------+------+
| a     | b    |
+-------+------+
| 00001 |    2 |
| 00002 |    3 |
| 00004 |    5 |
| 00005 |    6 |
| 00007 |    8 |
| 00008 |    9 |
| 00009 |   10 |
| 00010 |   11 |
| 00012 |   13 |
| 00013 |   14 |
| 00014 |   15 |
| 00015 |   16 |
| 00016 |   17 |
| 00017 |   18 |
| 00018 |   19 |
| 00033 |   34 |
| 00077 |   78 |
| 00555 |  556 |
+-------+------+
18 rows in set (0.00 sec)

MariaDB []> -- After DELETE a ROW
MariaDB []> UPDATE abc , (SELECT @nr:=0) AS INIT SET a := @nr := (@nr+1);
Query OK, 16 rows affected (0.03 sec)
Rows matched: 18  Changed: 16  Warnings: 0

MariaDB []> ALTER TABLE abc AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB []> select * from abc;
+-------+------+
| a     | b    |
+-------+------+
| 00001 |    2 |
| 00002 |    3 |
| 00003 |    4 |
| 00004 |    5 |
| 00005 |    6 |
| 00006 |    7 |
| 00007 |    8 |
| 00008 |    9 |
| 00009 |   10 |
| 00010 |   11 |
| 00011 |   12 |
| 00012 |   13 |
| 00013 |   14 |
| 00014 |   15 |
| 00015 |   16 |
| 00016 |   17 |
| 00017 |   18 |
| 00018 |   19 |
+-------+------+
18 rows in set (0.00 sec)

MariaDB []> INSERT INTO abc (a) VALUES(NULL);
Query OK, 1 row affected (0.01 sec)

MariaDB []> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               19 |
+------------------+
1 row in set (0.00 sec)

MariaDB []> select * from abc;
+-------+------+
| a     | b    |
+-------+------+
| 00001 |    2 |
| 00002 |    3 |
| 00003 |    4 |
| 00004 |    5 |
| 00005 |    6 |
| 00006 |    7 |
| 00007 |    8 |
| 00008 |    9 |
| 00009 |   10 |
| 00010 |   11 |
| 00011 |   12 |
| 00012 |   13 |
| 00013 |   14 |
| 00014 |   15 |
| 00015 |   16 |
| 00016 |   17 |
| 00017 |   18 |
| 00018 |   19 |
| 00019 |   20 |
+-------+------+
19 rows in set (0.01 sec)

MariaDB []>

Hi again thanks for your help but I have specifically mentioned in my question that I am looking for solution other than using higher data type for id and updating the higher id values after deleting an id (like you have suggested here). — A J Brockberg, Feb 29 '16 at 14:34

is it possible to update the autoincrement primary key values to the vacant values for mysql table

4 Answers4