Automatically Generating Primary Key Values with Database Controls

The following topic describes how to auto-generate primary key values using an Oracle SEQUENCE and a SQL Server IDENTITY field. The topic also explains how to use these auto-generation techniques in conjunction with a Workshop database control.

The topics in this section are:


Primary Key Generation Using an Oracle Sequence

Oracle provides the 'sequence' utility to automatically generate unique primary keys.

In your Oracle database, you must create a sequence table that creates the primary keys, as shown in the following example:

    /**
     * @jc:sql statement::
     * CREATE SEQUENCE MY_ORACLE_SEQUENCE
     * START WITH 1 
     * NOMAZVALUE::
     */
    public void createSequence() throws SQLException;

This creates a sequence of primary keys, starting with 1, followed by 2, 3, and so forth. The sequence table in the example uses the default increment 1, but you can change this by specifying the increment keyword, such as increment by 3.

When inserting new records into a table, you can generate a new primary key value by referring to the next value in the sequence.

    /**
     * @jc:sql statement="INSERT INTO CUSTOMER VALUES (MY_ORACLE_SEQUENCE.NEXTVAL, {firstname}, {lastname})"
     */
    void insertCustomer(String firstname, String lastname);

When database processes the insert statement, it consults the sequence for the next value, and uses that value as the primary key value of the new record.

Primary Key Generation Using a SQL Server IDENTITY Field

In SQL Server (2000) you can use the IDENTITY keyword to indicate that a primary-key needs to be auto-generated. The following example shows a common scenario where the first primary key value is 1, and the increment is 1:

    /**
     * @jc:sql statement::
     * CREATE TABLE jobz
     * (
     *    job_id  smallint
     *       IDENTITY(1,1)
     *       PRIMARY KEY CLUSTERED,
     *    job_desc        varchar(50)     NOT NULL
     * )::
     */
    void createTable();

When new records are inserted into the table, SQL Server generates a primary key value: you do not need to pass any primary key value to the database when inserting records.

    /**
     * @jc:sql statement::
     * INSERT INTO jobz (job_desc)
     * VALUES ({description})::
     */
    public int insertJob(String description);

Related Topics

@ejbgen:automatic-key-generation Annotation