Howdy.

Not so long ago, I created a GUI for data storage using Java and of course JDBC. I followed a tutorial about how to insert database records to the database table. The tutorial said that before we’re inserting new record to the database, we should generate unique key as record identity. The generation process handled by Java code. The tutorial use System.currentTimeMilis() function to create the key. So, the code will be look like this:

//set new id
Number time = System.currentTimeMillis();
Integer id = (time.intValue()/10000);

The generated key then being inserted to the database.

As you may realized, database systems usually have their own key generation technique. For example AUTO_INCREMENT attribute in MySQL. I followed the tutorial and sure it works. But, I tried database-generation key, and it works too. So, why we should bother generate our own keys?

The O’Reilly Java Author gave an explanation about that. They said:

However, using the supported key generation tools of your database of choice presents several problems:

• Every database engine handles key generation differently. Thus, it is difficult to build a truly portable JDBC application that uses proprietary key generation schemes.
• Until JDBC 3.0, a Java application had no clear way of finding out which keys were generated on an insert.
• Automated key generation wreaks havoc with EJBs.

I got the point that the application should be portable. Who knows that someday we’ll migrating from one database systems to another? So the database generation key will be hard to control. 

Another detailed answer came from Scott Selikoff. He wrote a complete article about database key generation and gave an example:

Now, let’s say a user is in the process of creating a new record in your system. For each user record, you also have a set of postal addresses. For example, Bob may be purchasing items on NewEgg and have a home address and a work address. Furthermore, Bob enters his two addresses at the time he creates his account, so the application server receives the information to create all 3 records at once. In such a situation, you would normally have 3 records: 1 user record for Bob and 2 address records. You could add the address info in the user table, although then you have to restrict the number of addresses Bob can have and/or have a user table with a lot of extra columns.

Inserting Bob into the user table is straight forward enough, but there is a problem when you go to insert users into the address table, namely that you need Bob’s newly generated User Id in order to insert any records into the address table. After all, you can’t insert addresses without being connected to a specific user, lest chaos ensue in your data management system.

The problems will arise in complex database relationship. As we don’t know the generated key, we couldn’t set the foreign key of corresponding tables. I didn’t realize this because the GUI I made was using a simple database design. Maybe with no table relation at all.

So, I got my question answered. Do you have another answer for my question? Feel free to share.