• Post Reply Bookmark Topic Watch Topic
  • New Topic

Trying to Avoid Duplicate Key Issues  RSS feed

 
Ranch Hand
Posts: 1785
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I cannot figure out how in the world the following is throwing a Duplicate Key error.



the value for ni is a 13 digit number that represents mills. Would more than one connection have to be tried at the same millisecond for this duplicate to be thrown?
Getting this message: AbstractSingleLevelBill: faiComponentDataModel: com.ibm.websphere.ce.cm.DuplicateKeyException: [SQL0803] Duplicate key value specified.
 
Sheriff
Posts: 21135
87
Chrome Eclipse IDE Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Steve Dyke wrote:

FYI, the same can be done using a single statement that also doesn't create an unnecessary Date object:

the value for ni is a 13 digit number that represents mills. Would more than one connection have to be tried at the same millisecond for this duplicate to be thrown?

It's not just possible for the milliseconds to be non-unique, it's highly probably. That's because a lot of statements take a lot less than 1ms to complete, and moreover, the number of milliseconds is not guaranteed to be accurate. It's quite possible that you will miss increases in the number of milliseconds, instead seeing jumps of up to 10ms.

In other words - unless you can guarantee in some other way that you will never insert records at the same millisecond (and you probably can't guarantee that), don't use any time-based value as a primary key. I'd just use the RDBMS' own mechanism of generating unique primary keys. That'd most likely be an auto-incrementing or sequence-generated value.
 
Steve Dyke
Ranch Hand
Posts: 1785
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rob Spoor wrote:
Steve Dyke wrote:

In other words - unless you can guarantee in some other way that you will never insert records at the same millisecond (and you probably can't guarantee that), don't use any time-based value as a primary key. I'd just use the RDBMS' own mechanism of generating unique primary keys. That'd most likely be an auto-incrementing or sequence-generated value.


This was my first choice but I could not figure out how to do an insert and return the generated index in one sql statement.
 
Marshal
Posts: 56600
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Surely you can set up a key with something like AUTOINCREMENT in the SQL. That way the database program will ensure uniqueness.
 
Steve Dyke
Ranch Hand
Posts: 1785
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Campbell Ritchie wrote:Surely you can set up a key with something like AUTOINCREMENT in the SQL. That way the database program will ensure uniqueness.


There is a function like this in DB2.

But two problems.

You cannot add this functionally to existing tables(files), only to tables(files) created with SQL.

Second, if I add a record how do I know which index I just created?
 
Ranch Foreman
Posts: 3068
37
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First off, please use a PreparedStatement rather than concatenating your variables into a SQL string.
It's a lot more reliable, and definitely less messy.

As for getting the generated key, there's a method when you created the statement which tells the driver to return generated keys.

And then, after executing the statement, you can call a method on the statement to retrieve those keys.
 
Java Cowboy
Sheriff
Posts: 16060
88
Android IntelliJ IDE Java Scala Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:It's a lot more reliable, and definitely less messy.

Not just more reliable, it also protects you against SQL injection, a well-known security vulnerability.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!