After deleting a row I want the primary key to start from 1 again.So is it possible

Question

Below is a sample of what I did

 {   
  ...

Class.forName("com.mysql.jdbc.Driver").newInstance();
    con=DriverManager.getConnection(ConnectionStr,"root","root");

    String prepareStr="DELETE FROM customer_maintenance where id=?";
    PreparedStatement pst= con.prepareStatement(prepareStr);
    pst.setInt(1,id);
    pst.executeUpdate();
   String update_key="SET @count = 0; UPDATE customer_maintenance SET customer_maintenance.id = @count:= @count + 1;";  

PreparedStatement   pst1=con.prepareStatement(update_key);
    pst1.executeUpdate();
System.out.println("UPDATE");


 }

It is throwing the following exception

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException:
You have an error in your SQL syntax

and I am not sure if it is possible to update the primary key, every time a row is deleted.

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; — Sangharsh Ranpise, Mar 13 '17 at 14:39
if u have this field set to AutoIncrement, don't miss with it, just leave it. when you create relations (if u will) later, this could make them invalid, when primaryKey-forignKey wise. jsut leave it as is, u will get used to it :) — Yazan, Mar 13 '17 at 14:39

Stefano Zanini · Accepted Answer · 2017-03-13T14:51:50.523

0

You should provide the exception you get, but the first problem I see in your code is that you may try to update a row setting ID = x, while another row could still have ID = x, thus an error for duplicate key.

That's because that update statement won't guarantee that the rows are updated following the ID order.

With the values in your example:

Start  Delete    Update1   Update2
ID     ID        ID        ID
1        (del)
2      2         2         2
3      3         1 (upd)   1
4      4         4         2 (upd)  <- duplicate key

Edit

Easiest fix is adding an order by clause to your query, so that the rows are update starting from the smaller ID.

You should also get rid of the ; at the end of the query.

String update_key="SET @count = 0;
    UPDATE customer_maintenance
    SET customer_maintenance.id = @count:= @count + 1
    ORDER BY customer_maintenance.id";

edited Mar 13 '17 at 14:51

answered Mar 13 '17 at 14:37

Stefano Zanini

5,876
2
13
33

Yes sir i got what you said and that was really helpful..But can u help me or give me some hint to solve the problem – Sangharsh Ranpise Mar 13 '17 at 14:44
And when i use this query "SET @count = 0; UPDATE ........." directly in mysql it works fine – Sangharsh Ranpise Mar 13 '17 at 14:47
Edited my answer with a solution – Stefano Zanini Mar 13 '17 at 14:52
Thank you sir...i ll try this – Sangharsh Ranpise Mar 13 '17 at 14:55
Have you configured your database connection to allow multiple queries? If not, read here http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement – Stefano Zanini Mar 13 '17 at 15:45
Glad it helped! If this or any answer has solved your question please consider [accepting it](http://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. – Stefano Zanini Mar 13 '17 at 16:03

score 0 · Answer 2 · answered Mar 13 '17 at 15:11

0

If you have set auto increment and primary key for Id column while table creation. Auto increment will handle this automatically while database deletion, no need to worry about it.

answered Mar 13 '17 at 15:11

Himanshu

117
2
5

what i want is,suppose there are 4 pk [1,2,3,4] and i d delete pk[1] than i want the new pk as [1,2,3,4] but what i am getting is [2,3,4] – Sangharsh Ranpise Mar 13 '17 at 15:15

After deleting a row I want the primary key to start from 1 again.So is it possible

2 Answers2