File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes duplicate created  in AUTO INCREMENT in mysql if its possible? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "duplicate created  in AUTO INCREMENT in mysql if its possible?" Watch "duplicate created  in AUTO INCREMENT in mysql if its possible?" New topic
Author

duplicate created in AUTO INCREMENT in mysql if its possible?

prasad guna
Ranch Hand

Joined: Feb 12, 2009
Posts: 43

Hi,

In my Project i use table "exampletrail" its have one composite key one of the primary key is "entrytime(timestamp)" another one is "instanceid (integer) - AUTO INCREMENT".my issue is Autoincrement field instanceid created one duplicate value. i dont know whether if its possible or not? yes means "how?". I need clear clarification.


Thanks in advance


Prasath.G
SCJP
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 40052
    
  28
why are you using time entered as part of a primary key?
prasad guna
Ranch Hand

Joined: Feb 12, 2009
Posts: 43


Hi Campbell,

This is necessory and it can be maintain uniqueness. but sometime it can make duplicate so that we create instance id with auto increment (for composite). i want to know if its possible the auto increment create the duplicate.


thanks
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Auto increment can't create duplicates.

If I were implementing this I would not use a composite key, since you already have a surrogate key in the auto increment field. If you need a unique index on those two fields, use a unique index. But life is usually easier in the long run if you use a simple surrogate key as primary key.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
prasad guna
Ranch Hand

Joined: Feb 12, 2009
Posts: 43


Hi,



I Know but in that scenario ok.
but wherever the auto increment is part of the composite key it will create the duplicate.are you sure about this.



thanks
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


but wherever the auto increment is part of the composite key it will create the duplicate

What makes you think this? Why does auto increment being used as part of a composite key imply duplicates?
prasad guna
Ranch Hand

Joined: Feb 12, 2009
Posts: 43


Paul

.... auto increment is part of the composite key it will create the duplicate ....


Take this scenario and answer me .because the duplicaton created in my table that's why i create the thread here.

Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Like I said before: Auto increment can't create duplicates. You'll get the details of this if you read the MySQL documentation (which I'm not going to repeat here).
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

prasad guna wrote:
Take this scenario and answer me .because the duplicaton created in my table that's why i create the thread here.


Are you sure the duplicated value was not created by another process? Eg. if the next value would be 100 and the table already contained a row with value 100, it would appear as if the error was caused by the insert statement, while in fact the problem could be that someone inserted or updated a row outside of your application.

(I don't know MySQL, but I assume that is is possible to change value of an AUTO INCREMENT column.)
prasad guna
Ranch Hand

Joined: Feb 12, 2009
Posts: 43

Martin,

Thanks for reply only one process for inserting rows through hibernate. I find some where while using on auto increment as a part of composite it will create the duplicates because it's not violate the composite rules so it will not keep the uniqueness.but i dont know the exact reason for that.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I find some where while using on auto increment as a part of composite it will create the duplicates because it's not violate the composite rules so it will not keep the uniqueness.but i dont know the exact reason for that.

Like I said before, auto increment will not generate duplicates. I would examine what you are doing to cause this - are you assigning values to the auto increment field?
prasad guna
Ranch Hand

Joined: Feb 12, 2009
Posts: 43

i give example for that below i give the sql query run and check that,



This was taken from my table.I Use Hibernate i am not assigning the values to autoincrement column.its generate automatically.above data "3901" is a duplicate why its created there.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Can you post your Hibernate code? There should not be a value in your insert statement for the auto increment field at all.
prasad guna
Ranch Hand

Joined: Feb 12, 2009
Posts: 43

This is code for inserting the rows




in hibernate i use ,
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Hmm. You sure about the database engine you are using? The behaviour you are seeing is only supposed to be possible with the MyISAM engine.
prasad guna
Ranch Hand

Joined: Feb 12, 2009
Posts: 43

Yes i am using linux, MYISAM is the default engine. if any other possible way to protect this.mysql version is 5.1.40
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: duplicate created in AUTO INCREMENT in mysql if its possible?