Lesson 2.4: Use automatic key generation

In the previous lesson, you set up navigation rules to ensure that users entered a unique ID for the new classified ad. This process is frustrating and unrealistic, because you would not want your site users to find a unique ID through trial and error. In this lesson, you will set up automatic key generation so that the database assigns a unique number for each new record in the database automatically.

Automatic key generation is a complex topic, but in short, a database can choose new keys if it has a special table reserved for key generation. This table must have a list of unused keys in one column (the incrementor column) and a list of numbers in order starting with 1 in the other column (the identity column). When the database needs a new key, it takes the key from the row with the 1 in the identity column and then gets a new key ready for the next time.

Learn more about automatic key generation

In order to set up automatic key generation, you must know a few things about how it works. To use automatic key generation, the database must have a table set aside for this purpose. This table has two columns:

The incrementor column stores available keys. When the database needs a new unique key, it retrieves one from this column.
The identity column is a list of numbers with only one instance of the number 1. This column tells the database which key to select from the incrementor column. The identity column is the primary key of the key generation table.

When the database needs a new unique key, it finds the row with an identity column value of 1. This row's incrementor column value holds the next available key. The database uses this key and updates the table so a new key will be available next time.

Here is an example of a key generation table. What is the next available key for this database? The answer is below the table.

Table 1. Key generation table
Identity Column	Incrementor Column
3	78
4	3
1	65
2	12

The next available key in this table is 65, because that key (in the incrementor column) is in the same row as the 1 in the identity column.

After the next available key is fetched from the table, the table is updated for the next time a key is needed. The database can also retrieve multiple keys at once by taking the incrementor column value of more than one row.

In short, for automatic key generation to work, you need only have a key generation table set up with two columns: a primary key column for use as an identity column, and a column for storing the next available key. This table must be initialized with one record whose identity column value is 1 and whose incrementor column value is the first available key to use. Once you have this set up, you are ready to use automatic key generation.

The sample database provided with this tutorial has a key generation table named KEYS. Its two columns, as described above, will supply a new ID number for each new record. In these steps, you will set up the create_record relational record to take its ID number from the KEYS table.

Double-click the new_record.jsp page in the Project Explorer view.
Double-click the create_record relational record in the Page Data view. The Configure Relational Record window opens.
Click Reuse metadata definition from an existing record or record list and Fill record with existing data from the database then click Next.
Select your database connection from the drop down list, then click Next.
In the Configure Relational Record window, on the right is a list of links. Click Auto generate key. The Key generation window opens.
Select Use Auto Key Generation.
Click to expand the W5SAMPLE list, then click the KEYS table.
In the Select identity column field, click KEY_ID.
In the Select incrementor column field, click NEXT_KEY.
Since you only need one key for the ad's ID, you will leave the "Keys fetched at once" setting at 1. If you needed multiple keys, this setting would make the database select all of them at once.

The Configure Data Object window should look like this:
Click Close to close the Key generation window, then Finish to apply the changes. Now the ID field will be automatically generated for each new record. Next you must remove the ID input field so the user cannot enter a value.
Place the cursor in the top row of the input form table by clicking on the Id: text.
Click Table > Delete > Delete Row.
Save the page.

Optionally, if you wanted to see what key is being generated for you, you could instead delete the input component for the ID number and replace it with an output component bound to the ID column of create_record. Then, the automatically generated key would appear at the top of the form but the user would not be able to change it.

When you are ready to publish your Web application, you will need a server that will host it so that users can access the Web site through the Internet. However, to test your Web site, you can use an available runtime to simulate a server for testing purposes. To find out how to test the Web site, refer to Lesson 1.3: Test the Web site.

You have completed Lesson 2.4. In this lesson you learned how to set up automated key generation.

Lesson 2.4: Use automatic key generation

Set up the automatic key generation

Test the completed tutorial

Lesson checkpoint