Auto Generated Primary Keys in Oracle

Unlike most other databases, Oracle does not provide an auto-incrementing datatype that can be used to generate a sequential primary key value. However, the same effect can be achieved with a sequence and a trigger.

This article demonstrates how to generate a sequential primary key and how to access this value using PL/SQL, JDBC, JPA and Hibernate.

The Table

We will use this simple account table for the demonstration.

CREATE TABLE acc( acc_id NUMBER(16),
                  acc_name VARCHAR2(30) NOT NULL,
                  acc_balance NUMBER DEFAULT 0 NOT NULL,
                  CONSTRAINT acc_pk PRIMARY KEY (acc_id));

Table created.

When an account is added, the database must generate the account identifier and return it to the application.

The Sequence and the Trigger

First, we create the sequence and the trigger which will populate the primary key column. The account identifiers will start from 2000.

CREATE SEQUENCE acc_id_seq START WITH 2000;

Sequence created.

CREATE OR REPLACE TRIGGER acc_pk_trg
  BEFORE INSERT ON acc FOR EACH ROW
BEGIN 
    IF :NEW.acc_id IS NULL THEN
      SELECT acc_id_seq.NEXTVAL INTO :NEW.acc_id FROM DUAL;
    END IF;
END;
/

Note that the sequence is only used if no value is provided for acc_id. This gives us some flexibility when inserting rows into the table as we will see later.

Incidentally, SQL Developer includes an option to create this type of trigger for us. Right click on a table in the Connections navigator to access the following menu.

Inserting Rows

When inserting rows into the table, we can specify the acc_id ourselves or have the value generated for us.

INSERT INTO acc (acc_id, acc_name)
 VALUES (1000, 'Green Square');

1 rows inserted

INSERT INTO acc (acc_name)
 VALUES ('Red Triangle');

1 rows inserted

SELECT * 
FROM acc;

ACC_ID       ACC_NAME         ACC_BALANCE            
------------ ---------------- ---------------- 
1000         Green Square     0                      
2000         Red Triangle     0

2 rows selected

Inserting our own primary key values will be an issue if we use a number which is later generated by the sequence. In this case, we may need to adjust the sequence to a value beyond the maximum primary key value.

Although there is an ALTER SEQUENCE statement which allows us to change pretty much every aspect of the sequence, it does not allow us to change the actual sequence number itself. To change this value you must drop and recreate the sequence using the START WITH clause.

PL/SQL

The following PL/SQL function adds a row to the table and returns the identifier of the newly created account to the calling application.

CREATE OR REPLACE 
FUNCTION add_acc (p_acc_name IN acc.acc_name%TYPE, 
                  p_balance IN acc.acc_balance%TYPE DEFAULT 0) 
                                                    RETURN NUMBER AS 
  l_acc_id acc.acc_id%TYPE;
BEGIN

  INSERT INTO acc (acc_name, acc_balance) 
     VALUES (p_acc_name, p_balance)
     RETURNING acc_id INTO l_acc_id;

  RETURN l_acc_id;
  
END;
/
 
EXEC DBMS_OUTPUT.PUT_LINE('Account id: ' || 
                               add_acc('Red Triangle', 10000));
COMMIT;
 
------------------------------------------------------------------------
 
Account id: 2000

The RETURNING clause on the INSERT statement is used to populate a local variable with the generated acc_id. This is then used as the return value from the function.

We can also call this function from Java.

CallableStatement cstmt = 
        con.prepareCall("BEGIN ? := add_acc(?,?); END;");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, "Green Square");
cstmt.setInt(3, 2000);
cstmt.execute();
System.out.println("Account Id: " + cstmt.getInt(1));
 
------------------------------------------------------------------------
 
Account id: 2001

JDBC

JDBC Auto-Generated Keys

JDBC 3.0 introduced the retrieval of auto-generated keys. When calling execute or executeUpdate on a Statement object or prepareStatement on a Connection, we can specify which values we wish to return from our INSERT statement.

In this code snippet we request both the acc_id and the acc_balance to show that we can return multiple values and not just the generated identifier. We also use named bind variables rather than question marks, but either style is acceptable.

PreparedStatement pstmt = 
 con.prepareStatement("INSERT INTO acc (acc_name) " +
                        "VALUES (:acc_name)", 
                             new String[] { "acc_id", "acc_balance" });
 
pstmt.setString(1, "Red Triangle");
pstmt.executeUpdate();
 
ResultSet rs = pstmt.getGeneratedKeys();
if (rs.next()) {
    System.out.println("Account Id: " + rs.getInt(1));
    System.out.println("Balance: " + rs.getInt(2));
}
 
------------------------------------------------------------------------
 
Account Id: 2002
Balance: 0

The example returns a ResultSet containing a single row with a column for each requested value.

Some databases allow you to specify the RETURN_GENERATED_KEYS constant rather than naming all the generated keys explicitly. However, this feature does not work with Oracle as the JDBC driver cannot identify which columns need to be retrieved. Instead, Oracle will return the ROWID.

PreparedStatement pstmt = 
 con.prepareStatement("INSERT INTO acc (acc_name) " +
                             "VALUES (:acc_name)", 
                             Statement.RETURN_GENERATED_KEYS);
 
pstmt.setString(1, "Red Triangle");
pstmt.executeUpdate();
 
ResultSet rs = pstmt.getGeneratedKeys();
if (rs.next()) {
    System.out.println(“Generated key: " + rs.getString(1));
}
 
------------------------------------------------------------------------
 
Generated key: AAA9TIAAGAAJqNfAAV

DML Returning

We can also return data from an INSERT statement using the RETURNING clause as we saw in the PL/SQL example earlier. Standard JDBC does not support DML returning directly with PreparedStatement so we need to use OraclePreparedStatement instead.

OraclePreparedStatement pstmt = (OraclePreparedStatement)
    con.prepareCall("INSERT INTO acc (acc_name, acc_balance) " +
                    "VALUES (:acc_name, :acc_balance) " +
                    "RETURNING acc_id INTO :acc_id");
 
pstmt.setString(1, "Green Square");
pstmt.setInt(2, 1500);
pstmt.registerReturnParameter(3, OracleTypes.NUMBER);
pstmt.executeUpdate();
 
ResultSet rs = pstmt.getReturnResultSet();
if (rs.next()) {
    System.out.println("Account Id: " + rs.getInt(1));
}
 
------------------------------------------------------------------------
 
Account Id: 2003

One benefit of using this technique is that it can be used with UPDATE and DELETE statements too, whereas the JDBC auto-generated key retrieval feature can only be used with an INSERT.

OraclePreparedStatement pstmt = (OraclePreparedStatement) 
    con.prepareCall("UPDATE acc " +
                    "SET acc_balance = acc_balance + :bal_adjustment " +
                    "WHERE acc_id = :acc_id " +
                    "RETURNING acc_balance INTO :balance");
                        
pstmt.setInt(1, 1000);               
pstmt.setInt(2, 2003);
pstmt.registerReturnParameter(3, OracleTypes.NUMBER);
pstmt.executeUpdate();
 
ResultSet rs = pstmt.getReturnResultSet();
if (rs.next()) {
    System.out.println("New Balance: " + rs.getInt(1));
}
 
------------------------------------------------------------------------
 
New Balance: 2500

Object Relational Mapping

Next we take a regular Java class and persist it to the database, first using the Java Persistence API (JPA) and then using the Hibernate proprietary API.

This simple class represents an account.

public class Account {

    private int id;
    private String name;
    private int balance;
 
    ...
 
}

Java Persistence API

To generate a sequential primary key for our JPA entity we add the following annotations to the Account class.

@Entity(name = "acc")
public class Account {
 
    @Id
    @Column(name="ACC_ID", nullable = false)
    @GeneratedValue(generator = "AccountSeq", 
                    strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "AccountSeq", 
                       sequenceName = "ACC_ID_SEQ", 
                       allocationSize = 1)
    private int id;
 
    @Column(name="ACC_NAME")
    private String name;
 
    @Column(name="ACC_BALANCE")
    private int balance;
 
    ...
 
}

The @GeneratedValue annotation tells the JPA vendor that we are using a sequence to generate the primary key. The @SequenceGenerator annotation specifies that we are using the ACC_ID_SEQ sequence.

Saving our object to the database with JPA is simple.

EntityManagerFactory emf = 
         Persistence.createEntityManagerFactory("accounts");
EntityManager em = emf.createEntityManager();
 
EntityTransaction txn = em.getTransaction();
txn.begin();
 
Account acc = new Account();
acc.setName("Red Triangle");
em.persist(acc);
txn.commit();
 
System.out.println("Account Id: " + acc.getId());
 
------------------------------------------------------------------------
 
Account Id: 2004

When we persist the object to the database, the account identifier will be populated automatically in both the Java object and the database table.

Hibernate

With Hibernate, we can use annotations as we did in the previous example, or we can use a mapping file.

<hibernate-mapping>
    <class name="blog.Account" table="ACC">
        <id name="id" column="ACC_ID">
            <generator class="sequence">
                <param name="sequence">ACC_ID_SEQ</param>
            </generator>
        </id>
        <property name="name" column="ACC_NAME" />
        <property name="balance" column="ACC_BALANCE" />
    </class>
</hibernate-mapping>

The id element specifies the primary key column and the nested generator element specifies the name of the sequence we wish to use.

We then use the Hibernate API to save the Java object.

Session session =
    new AnnotationConfiguration().
        configure().
        buildSessionFactory().
        getCurrentSession();
 
session.beginTransaction();
 
Account acc = new Account();
acc.setName("Red Triangle");
acc.setBalance(1500);
int accId = (Integer)session.save(acc);
 
session.getTransaction().commit();
 
System.out.println("Account id: " + accId);
 
------------------------------------------------------------------------
 
Account id: 2005

Allocation Size

The allocationSize in the @SequenceGenerator annotation should be the same as the increment size of the sequence. For example, if the sequence is defined with the following SQL then the allocationSize should also be set to 20.

CREATE SEQUENCE acc_id_seq START WITH 2020 INCREMENT BY 20;

Each time the the JPA provider retrieves a value from the sequence, it can allocate 20 sequential identities to new objects before it needs to go back to the database to access the next sequence value.

For example, if the provider receives the value 100 from our sequence and the allocationSize is 20 it will allocate the values 81 to 100 to the next 20 Account objects that are persisted.

This feature reduces the number of round trips to the database which will improve application performance.

Optional Primary Key Generation

When persisting an object to the database, Hibernate or the JPA provider (which can also be Hibernate) will obtain the next number from the sequence and use this value in the INSERT statement.

We can see this if we enable SQL tracing with either Hibernate or Toplink.

Hibernate

Hibernate: select ACC_ID_SEQ.nextval from dual
Hibernate: insert into ACC (ACC_NAME, ACC_BALANCE, ACC_ID) values (?, ?, ?)

Toplink

(Thread[main,5,main])--SELECT acc_id_seq.NEXTVAL FROM DUAL
(Thread[main,5,main])--INSERT INTO ACC (acc_id, acc_name, acc_balance) VALUES (?, ?, ?)
 bind => [2001, Red Triangle, 1500]

At this point, the retrieved sequence value is also used to populate the identifier in the Java object.

We can now see the importance of making the primary key generation conditional in the trigger.

IF :NEW.acc_id IS NULL THEN
    SELECT acc_id_seq.NEXTVAL INTO :NEW.acc_id FROM DUAL;
END IF;

If we exclude the IS NULL condition, the trigger will effectively overwrite the value supplied for acc_id in the INSERT statement with the next value from the sequence. This would result in a primary key value and an object identifier which are out of sync.