Problem Source: Duplicate entry ' 0 ' for key ' PRIMARY ' error occurred while updating the IDs for all records in the datasheet.
Because the same table contains data stored by different users, different foreign keys.
An error occurred while updating the primary key.
Error Tip:
Com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:Duplicate entry ' 1 ' for key ' PRIMARY '
Analysis:
(1) The primary key ID has been set to grow automatically;
It is possible that the field PID is primary key and the Auto_increment property cannot have duplicate values.
(2) A data write corrupted data table.
Find the root cause:
Because the ID data is read first in the operation of the update ID, the data sort is not sorted in ascending order, so there is a conflict when rewriting it again.
WORKAROUND: Use ORDER BY in SQL statements.
Http://database.51cto.com/art/201108/284083.htm
The ORDER BY clause sorts query results by one or more (up to 16) fields, either ascending (ASC) or Descending (DESC), and the default is ascending. The order clause is usually placed at the end of the SQL statement. Multiple fields are defined in the order clause, sorted by field.
String str = "Select Dutyleaderid from Dutyleader order by Dutyleaderid";
rs = stmt.executequery (str);
list<integer> idlist = new arraylist<integer> ();
while (Rs.next ()) {
Idlist.add (rs.getint ("Dutyleaderid"));
}
if (idlist!= null && idlist.size () > 0) {
//stmt must close
stmt.close ();
stmt = Con.createstatement ();
for (int i = 0; i < idlist.size (); i++) {
//system.out.print (Idlist.get (i));
str = "Update dutyleader set dutyleaderid= '" + (i+1) + "' where dutyleaderid=" + idlist.get (i);
Stmt.executeupdate (str);
System.out.print ("\ n");
}
The primary key conflict is not present.