0

I just started learning jdbc and doing various tests with it.

I came across the following situation

  • set savepoint
  • create prepared statement and try to execute(insert data into a table with foreign key value that doesn't exist
  • the execute() method fails and I do a rollback.
  • when I do a valid query a row is inserted into the db, but the primary key is set as n+2 instead of n+1(where n is the last inserted value). Guess the index autoincrements even though the query fails.

Is there a mechanism to avoid this and set n+1?

Note. I am using MySQL.

Thanks.

Radu
  • 1,044
  • 3
  • 12
  • 35
  • 2
    how about reading http://stackoverflow.com/questions/449346/mysql-auto-increment-does-not-rollback topic? – Alex Stybaev Mar 21 '12 at 14:14
  • I agree with that post, but I don't want to take into consideration that in my app logic. I was just wondering why the phenomenon is happening and if there's a way to overcome this. – Radu Mar 21 '12 at 14:28
  • It happens because the id generation is independent of the transaction. There is no way to overcome it without a lot of effort. Don't assign any meaning to the id. – Hiro2k Mar 21 '12 at 14:58
  • And this is why Oracle databases don't have AUTO_INCREMENT fields. Use a trigger instead. – Eldelshell Apr 02 '12 at 09:49

1 Answers1

0

This behaviour is normal for MySQL auto_increment. Well, you can do some magic using mysql_insert_id().

Alex Stybaev
  • 4,623
  • 3
  • 30
  • 44