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..
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.
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.