Managing surrogate keys for parent and child tables

Many application databases will use surrogate keys as a means of uniquely identifying rows in a table. As a result of this, it is often necessary to capture the surrogate key for the record after writing a record, so that any associated child elements will be able to reference the parent's surrogate as a foreign key. This recipe shows one method of creating surrogate keys in a relation and later discusses few more methods.

Many aspects of this job should be familiar, especially if you have completed the previous recipes in this chapter, so only a few key points have been highlighted.

Getting ready

Open the job jo_cook_ch07_0110_surrogateKeys.

How to do it…

The steps to be performed are as follows:

1. Open the tMysqlInput component labeled globalCustomer, and add the following query:

   "
   SELECT COALESCE(MAX(customerId),0) FROM globalCustomer
   "

2. Open tJavaRow_1, and add the following code:

   globalMap.put("maxCustomerId",input_row.maxCustomerId);
   System.out...