Building Java Enterprise Applications

Primary Keys

The storage described in Chapter 5 for primary key generation requires an additional table in the database schema. The scripts in this section are product-specific versions of the SQL script shown in Example 5-1.

Warning

Since this table is added in Chapter 5, this section is presented after Section A.4 in this appendix. The scripts shown previously do not drop the table created in this section; instead, this script drops any existing table and then re-creates the storage. If you decide to use this methodology, be sure you do not rerun this script once the table it creates is in use; it effectively resets the primary key counters, and can cause duplicate keys to be added to the database. Another approach would be to write some more advanced SQL to determine the highest ID value, and then start the key values at a number greater than that found. However, this advanced SQL is beyond the scope of this book.

Cloudscape

The SQL script shown in Example A-21 creates the primary key storage table on Cloudscape databases.

Example A-21.  SQL Script for Creating the Primary Key Table on Cloudscape Databases

-- Drop any existing PRIMARY_KEYS table DROP TABLE PRIMARY_KEYS; -- PRIMARY_KEYS table CREATE TABLE PRIMARY_KEYS ( KEY_NAME VARCHAR(20) PRIMARY KEY NOT NULL, NEXT_VALUE INT NOT NULL ); -- Add initial values for each table INSERT INTO SEQUENCES VALUES ('USER_TYPES', 1); INSERT INTO SEQUENCES VALUES ('OFFICES', 1); INSERT INTO SEQUENCES VALUES ('USERS', 1); INSERT INTO SEQUENCES ...