• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

duplicate created in AUTO INCREMENT in mysql if its possible?

 
Ranch Hand
Posts: 46
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Marshal
Posts: 70211
280
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
why are you using time entered as part of a primary key?
 
prasad guna
Ranch Hand
Posts: 46
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
prasad guna
Ranch Hand
Posts: 46
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Posts: 46
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 46
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Posts: 46
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 46
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is code for inserting the rows




in hibernate i use ,
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 46
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes i am using linux, MYISAM is the default engine. if any other possible way to protect this.mysql version is 5.1.40
 
The moth suit and wings road is much more exciting than taxes. Or this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic