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 auto generate non-primary key Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "auto generate non-primary key" Watch "auto generate non-primary key" New topic
Author

auto generate non-primary key

michael adam
Greenhorn

Joined: Oct 25, 2012
Posts: 1
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

Joined: Jun 06, 2007
Posts: 2712
    
    5

Hi,
Welcome to Javaranch :)
Please CarefullyChooseOneForum to ensure you get proper response for your query.
Moving this to appropriate place.

SCJP, SCWCD.
|Asking Good Questions|
Amit Ghorpade
Bartender

Joined: Jun 06, 2007
Posts: 2712
    
    5

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

Joined: Aug 22, 2010
Posts: 3438
    
  47

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
 
subject: auto generate non-primary key
 
Similar Threads
Autonumber generation in jboss
Difference between id generators
primary key on jboss
CMP 2.0 - how to autogenerate a PKey?
How to create CMP beans for DB2 whose column is auto generate