Duplicate entry '0' for key 'PRIMARY' error

Source of the problem: When updating the Ids of all records in the data table, Duplicate entry '0' for key 'PRIMARY' error occurred.

Because there are data stored by different users in the same table and different foreign keys.

An error occurred while updating the primary key.

Error prompt:

.: Duplicate entry '1' for key 'PRIMARY'

analyze:

(1) The primary key Id has been set to automatically grow;

It may be that the field pid is primary key and the auto_increment attribute cannot appear duplicate values.
(2) Some data is written to destroy the data table.

Find the root cause:

Since the id data is read first in the update id operation, the data sorting is not arranged in ascending order, so there will be conflicts when rewriting it.

Solution: Use order by in sql statement.

http://database./art/201108/ 

The ORDER BY clause sorts the query results by one or more (up to 16) fields, which can be 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, and they are sorted in the order of the fields.

String str = "select DutyleaderId from dutyleader order by DutyleaderID";
			 rs = (str);
			 List<Integer> idlist = new ArrayList<Integer>();
			 while (()) {
				 (("DutyleaderId"));
			 }
			 if (idlist != null && () > 0) {
				 //Stmt must be closed
				 ();
				 stmt = ();
				 for (int i = 0; i < (); i++) {
					 //((i));
					 str = "update dutyleader set DutyleaderID='" + (i+1) + "' where DutyleaderID=" + (i);
					 (str);
				 }
				 //("\n");
			 }

This way there will be no primary key conflict.