Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

auto generate non-primary key

 
michael adam
Greenhorn
Posts: 1
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i've table called stub_master that consist of stub_mast_id(PK) and stub_ref_no etc.

For current situation, user must fill in the stub_ref_no before submit, and it has caused so many diff. names created by the users
To avoid this matter, i wanted to auto generate the ref no starting using this format /eg: stub_ref_no = '0000001'.

Problem is that, stub_ref_no is a non-primary key.
kindly help me on this..
 
Amit Ghorpade
Bartender
Posts: 2851
10
Fedora Firefox Browser Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Welcome to Javaranch :)
Please CarefullyChooseOneForum to ensure you get proper response for your query.
Moving this to appropriate place.
 
Amit Ghorpade
Bartender
Posts: 2851
10
Fedora Firefox Browser Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess this depends on the database also. Some databases do support this type of built-in field. If you are looking for a Java based solution then the simplest one would be to read the max value of that field and increment it by one during the insert/update. This is not a recommended approach though.
 
Martin Vajsar
Sheriff
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Amit Ghorpade wrote:If you are looking for a Java based solution then the simplest one would be to read the max value of that field and increment it by one during the insert/update. This is not a recommended approach though.

Indeed. Without proper locking and/or synchronization, you may end up getting identical keys for different users. With proper locking, the solution is inherently unscalable.

Most databases should have tools for this, either identity/autoincrement columns, or sequences. They can usually be used even for non-PK columns. You may run into problems with autoincrement columns if you need the column to be a VARCHAR, though. My advice would be to keep the column as NUMBER and doing conversion to String in the application. If you have existing data, you'll need to upgrade the schema, assign new IDs to existing rows and start from there.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic