Auto Increment after delete in MySQL

Question

I have a MySQL table with a primary key field that has AUTO INCREMENT on  After reading other posts on here I ve noticed people with the same problem and with varied answers  Some recommend not using this feature  others state it can t be  fixed    I have   table  course fields  courseID  courseName   Example  number of records in the table  18  If I delete records 16  17 and 18 - I would expect the next record entered to have the courseID of 16  however it will be 19 because the last entered courseID was 18   My SQL knowledge isn t amazing but is there anyway to refresh or update this count with a query  or a setting in the phpMyAdmin interface    This table will relate to others in a database     Given all the advice  I have decided to ignore this  problem   I will simply delete and add records whilst letting the auto increment do it s job  I guess it doesn t really matter what the number is since it s only being used as a unique identifier and doesn t have a  as mentioned above  business meaning   For those who I may have confused with my original post  I do not wish to use this field to know how many records I have  I just wanted the database to look neat and have a bit more consistency

User · Answer

here is a function that fix your problem      public static void fixID Connection conn  String table         try           Statement myStmt   conn createStatement            ResultSet myRs          int i   1  id   1  n   0          boolean b          String sql           myRs   myStmt executeQuery  select max id  from     table           if  myRs next                  n   myRs getInt 1                     while  i  lt   n                b   false              myRs   null              while   b                    myRs   myStmt executeQuery  select id from     table     where id     id                   if   myRs next                          id                      else                       b   true                                               sql    UPDATE     table     set id      i     WHERE id     id              myStmt execute sql               i                id                     catch  SQLException e            e printStackTrace

User · Answer

I got a very simple but tricky method    While deleting a row  you can preserve the IDs into another temporary table  After that  when you will insert new data into the main table then you can search and pick IDs from the temporary table  So use a checking here  If the temporary table has no IDs then calculate maximum ID into the main table and set the new ID as  new ID   old max ID 1   NB  You can not use auto-increment feature here

User · Answer

MYSQL Query Auto Increment Solution  It works perfect when you have inserted many records during testing phase of software  Now you want to launch your application live to your client and You want to start auto increment from 1  To avoid any unwanted problems  for safer side First export  sql file  Then follow the below steps   Step 1  First Create the copy of an existing table MySQL Command to create Copy  CREATE TABLE new Table Name  SELECT   FROM existing Table Name   The exact copy of a table is created with all rows except Constraints  It doesn   t copy constraints like Auto Increment and Primary Key into new Table name  Step 2  Delete All rows If Data is not inserted in testing phase and it is not useful  If Data is important then directly go to Step 3  DELETE from new Table Name    Step 3  To Add Constraints  Goto Structure of a table  3A  Add primary key constraint from More option  If You Require   3B  Add Auto Increment constraint from Change option  For this set Defined value as None  3C  Delete existing Table Name and 3D  rename new Table Name to existing Table Name     Now It will work perfectly  The new first record will take first value in Auto Increment column

User · Answer

Try    SET   num    0  UPDATE your table SET id    num      num 1   ALTER TABLE tableName AUTO INCREMENT   1   That ll reset the autoincremented value  and then count every row while a new value is created for it  example   before  1   first value here 2   second value here X   deleted value 4   The rest of the table 5   The rest of the rest    so the table will display the array   1 2 4 5 Example   AFTER  if you use this command you will obtain   1   first value here 2   second value here 3   The rest of the table 4   the rest of the rest  No trace of the deleted value  and the rest of the incremented continues with this new count  BUT  If somewhere on your code something use the autoincremented value    maybe this attribution will cause problem  If you don t use this value in your code everything should be ok

User · Answer

Primary autoincrement keys in database are used to uniquely identify a given row and shouldn t be given any business meaning  So leave the primary key as is and add another column called for example courseOrder  Then when you delete a record from the database you may want to send an additional UPDATE statement in order to decrement the courseOrder column of all rows that have courseOrder greater than the one you are currently deleting   As a side note you should never modify the value of a primary key in a relational database because there could be other tables that reference it as a foreign key and modifying it might violate referential constraints

User · Answer

you can select the ids like so   set  rank   0  select id   rank   rank 1 from tbl order by id   the result is a list of ids  and their positions in the sequence   you can also reset the ids like so   set  rank   0  update tbl a join  select id   rank   rank 1 as rank from tbl order by id  b   on a id   b id set a id   b rank    you could also just print out the first unused id like so   select min id  as next id from   select a id from  select 1 as id  a   left join tbl b on a id   b id where b id is null  union    select min a id    1 as id from tbl a left join tbl b on a id 1   b id   where b id is null   c    after each insert  you can reset the auto increment   alter table tbl auto increment   16   or explicitly set the id value when doing the insert   insert into tbl values  16   something      typically this isn t necessary  you have count    and the ability to create a ranking number in your result sets  a typical ranking might be   set  rank   0  select a name  a amount  b rank from cust a     select amount   rank   rank 1 as rank from cust order by amount desc  b   where a amount   b amount   customers ranked by amount spent

User · Answer

What you are trying to do is very dangerous  Think about this carefully  There is a very good reason for the default behaviour of auto increment    Consider this   A record is deleted in one table that has a relationship with another table  The corresponding record in the second table cannot be deleted for auditing reasons  This record becomes orphaned from the first table  If a new record is inserted into the first table  and a sequential primary key is used  this record is now linked to the orphan  Obviously  this is bad  By using an auto incremented PK  an id that has never been used before is always guaranteed  This means that orphans remain orphans  which is correct

User · Answer

Its definitely not recommendable  If you have a large database with multiple tables  you may probably have saved a userid as id in table 2  if you rearrange table 1 then probably the intended userid will not end up being the intended table 2 id

User · Answer

What you re trying to do sounds dangerous  as that s not the intended use of AUTO INCREMENT   If you really want to find the lowest unused key value  don t use AUTO INCREMENT at all  and manage your keys manually  However  this is NOT a recommended practice   Take a step back and ask  why you need to recycle key values   Do unsigned INT  or BIGINT  not provide a large enough key space    Are you really going to have more than 18 446 744 073 709 551 615 unique records over the course of your application s lifetime

User · Answer

You can use your mysql client software script to specify where the primary key should start from after deleting the required records

User · Answer

You may think about making a trigger after delete so you can update the value of autoincrement and the ID value of all rows that does not look like what you wanted to see   So you can work with the same table and the auto increment will be fixed automaticaly whenever you delete a row the trigger will fix it

User · Answer

if  id    1      deleting first row             mysqli query  db  UPDATE employees  SET id id-1 WHERE id gt 1                      else if  id gt 1  amp  amp   id lt  num      deleting middle row             mysqli query  db  UPDATE employees  SET id id-1 WHERE id gt  id                      else if  id     num      deleting last row             mysqli query  db  ALTER TABLE employees AUTO INCREMENT    num                      else              echo  ERROR                      mysqli query  db  ALTER TABLE employees AUTO INCREMENT    num

User · Answer

There is actually a way to fix that  First you delete the auto incremented primary key column  and then you add it again  like this   ALTER TABLE table name DROP column name  ALTER TABLE table name ADD column name int not null auto increment primary key first

User · Answer

ALTER TABLE foo AUTO INCREMENT 1   If you ve deleted the most recent entries  that should set it to use the next lowest available one   As in  as long as there s no 19 already  deleting 16-18 will reset the autoincrement to use 16     EDIT  I missed the bit about phpmyadmin   You can set it there  too   Go to the table screen  and click the operations tab   There s an AUTOINCREMENT field there that you can set to whatever you need manually

User · Answer

I can think of plenty of scenarios where you might need to do this  particularly during a migration or development process   For instance  I just now had to create a new table by cross-joining two existing tables  as part of a complex set-up process   and then I needed to add a primary key after the event  You can drop the existing primary key column  and then do this   ALTER TABLE my table ADD  ID  INT NOT NULL AUTO INCREMENT FIRST  ADD PRIMARY KEY   ID      For a live system  it is not a good idea  and especially if there are other tables with foreign keys pointing to it

User · Answer

You shouldn t be relying on the AUTO INCREMENT id to tell you how many records you have in the table  You should be using SELECT COUNT    FROM course  ID s are there to uniquely identifiy the course and  can be used as references in other tables  so you shouldn t repeat ids and shouldn t be seeking to reset the auto increment field

User · Answer

I came here looking for an answer to the Title question  MySQL - Auto Increment after delete  but I could only find an answer for that in the questions   How to delete certain row from mysql table  How to reset AUTO INCREMENT in MySQL    By using something like   DELETE FROM table  ALTER TABLE table AUTO INCREMENT   1      Note that Darin Dimitrov s answer explain really well AUTO INCREMENT and it s usage  Take a look there before doing something you might regret   PS  The question itself is more  Why you need to recycle key values   and Dolph s answer cover that

[sql] Auto Increment after delete in MySQL

Examples related to sql

Examples related to mysql

Examples related to primary-key

Examples related to auto-increment