An Alternate to Auto Increment

No Auto-Increment, Generate Unique Id by own.

Auto-Increment: A column in a database table can be specified as auto-increment, which means the value for that column is auto incremented everytime a new record is inserted. It may be useful for some applications and at the same time pose some issues in some applications.
For eg., Consider a simple schema with tables Person, PersonAddress. Schema as given below

 PERSON
  ID INTEGER(5) AUTO-INCREMENT,
  NAME VARCHAR (50)
 
 PERSON_ADDRESS
  ID  INTEGER (5) AUTO-INCREMENT,
  USER_ID  INTEGER(5) REFERENCES PERSON (ID),
  ADDRESS1 VARCHAR (50),
  CITY  VARCHAR (20),
  COUNTRY  VARCHAR (20)

Consider a use-case where a user is submitting the details, & the code is storing those details into these 2 tables in database.
Storing these details is two step process, i.e., First, create a record in PERSON table, and then create a record in PERSON_ADDRESS table, the issue rises when we are to insert record to PERSON_ADDRESS, how do we get the USER_ID that we just created. Since the ID field in PERSON table is auto-incremented, so in the program we dont have the USER_ID right away. So, How do we get this newly created USER_ID to use for PERSON_ADDRESS table? One way is to make a SQL call to database & get the USER_ID for recently created record, but, this is not efficient way of doing it.
The solution I prefer is to NOT use auto-increment feature of the database. Generate the Unique id's for the ID fields by own. In this article I'll show how this can be done in a simple & generic way for the whole application.

1) Table to store recently Used ID's for each table:

CREATE TABLE SEQUENCE ( 
 TABLE_NAME STRING (40) PRIMARY KEY,
 CURRENT_VALUE INTEGER(10) DEFAULT 0
);

SEQUENCE table stores recently used ID for the TABLE_NAME. Everytime the value is fetched, CURRENT_VALUE is incremented by 1. This is done by the utility method. (Shown below)

2) A Utility Method to generate ID (In sequence)

public class DBUtil {
 
 public int generateNextId (String tableName) {
  /*
   Create connection object & statement objects
   Connection conn = .... ;
   Statement stmt = ... ;
  */
  int nextId = -1;

  String sql = "SELECT CURRENT_VALUE FROM SEQUENCE WHERE TABLE_NAME = '" + tableName + "'";

  ResultSet rs = stmt.executeQuery(sql);
  while(rs.next()) {
   int temp = rs.getInt (CURRENT_VALUE);
   nextId = temp + 1;
  }
  if(nextId == -1) return nextId;

  // INCREMENT the ID & store back in table
  String updateSql = "UPDATE FROM SEQUENCE SET CURRENT_VALUE = ? WHERE TABLE_NAME = '" + tableName + "'";
  PreparedStatement pstatement = connection.prepareStatement(updateSql);

  pstatement.setString(1, nextId);
  int status = pstatement.executeUpdate();

  return nextId;
 }
}

3)Example: Using Utility method while inserting data

 String personId = DBUtil.generateNextId ("PERSON");

 String insertPersonSQL = "INSERT INTO PERSON (ID, NAME) VALUES (?,?)";
 pstatement1 = connection.prepareStatement(insertPersonSQL);
 pstatement1.setString(1, personId);
 pstatement1.setString(2, name);

 // Now we still have person id handle, can use the same for Person Address record
 String addressId = DBUtil.generateNextId ("PERSON_ADDRESS");

 String insertAddressSQL = "INSERT INTO PERSON_ADDRESS (ID, PERSON_ID, ADDRESS, CITY) VALUES (?,?,?,?)";
 pstatement2 = connection.prepareStatement(insertPersonSQL);
 pstatement2.setString(1, addressId);
 pstatement2.setString(2, personId);
 .
 .

Here, in this example, when inserting new records, we generate ID for person table using the utility as DBUtil.generateNextId ("PERSON");, and we have handle for this new Id, which we use for further insertions when required. This wouldn't be possible with default auto-increment feature that database provides.

Hope this makes sense & it's useful. Any suggestions/query, feel free to contact me.