CODEWITHSUNDEEP
javamysqlsqljdbc
Periit
Periit

Reputation: 65

JDBC INSERT INTO - wrong primary key

when i execute code from below everything if fine, but my primary key should be setted to 2 but it jumped to 6 and db looks like id 1 and next is 6. i delete before few rows from this db and im pretty sure that is the problem. its looks like everything has been deleted but something still stay there.

con = DriverManager.getConnection("jdbc:mysql://localhost:3306/","root","");
stat = con.createStatement();
stat.executeUpdate("INSERT INTO money.state (bank,wallet) VALUES (200,400)");

please help.

Upvotes: 1

Views: 173

Answers (2)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

The expected behaviour is to increment a sequence for every record. When you delete any row or truncate the table (for Postgres) it doesn't mean that sequence is going to start from starting value, it just continues from the last value and increments it by the increment_by factor.

Consider this sequence as an example

         Sequence "public.test_a_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | test_a_seq
 last_value    | bigint  | 7
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 26
 is_cycled     | boolean | f
 is_called     | boolean | t

Now, I delete rows and add two rows with default values on PRIMARY KEY

After that factor of last_value would increment by 2.

last_value | bigint | 9

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172418

Primary key is auto incremented and if you delete the rows from your table then the next entry would be from the last primary key id which was deleted not from the last primary key present in the table. This is the way it works.

Howevere if you want to fill the gaps(which is not recommeneded) then you have to manually make the changes like this:

ALTER TABLE [yourtablename] AUTO_INCREMENT = 1

So this will reset the auto_increment value to be the next based on the highest existing value existing in the table.

Upvotes: 3

Related Questions