Tables with Primary Key Column "GENERATED ... AS IDENTITY"

JDBC Tutorials - Herong's Tutorial Examples

∟Tables with Primary Key Column "GENERATED ... AS IDENTITY"

This section describes how to create a table primary key column defined as 'GENERATED ... AS IDENTITY'.

Similar to Microsoft SQL Server, Derby supports IDENTITY columns to help you creating auto-incremented sequence values for primary key columns. Derby offers two variations of IDENTITY columns:

"GENERATED ALWAYS AS IDENTITY" - Derby always provides auto-incremented sequence values to this column. You are not allowed to specify your own values.
"GENERATED BY DEFAULT AS IDENTITY" - Derby provides auto-incremented sequence values to this as default only when you are not providing values.

In order to try IDENTITY columns and provide a test table for testing DML (Data Manipulation Statements), I wrote the following program to create a table called "Profile" with the primary key column defined as an IDENTITY column:

/* DerbyIdentityColumn.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class DerbyIdentityColumn {
  public static void main(String [] args) {
    Connection con = null;
    try {
      con = DriverManager.getConnection("jdbc:derby://localhost/TestDB");

// Creating a database table
      Statement sta = con.createStatement();
      int count = sta.executeUpdate(
        "CREATE TABLE Profile ("
        + " ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,"
        + " FirstName VARCHAR(20) NOT NULL,"
        + " LastName VARCHAR(20),"
        + " Point REAL DEFAULT 0.0,"
        + " BirthDate DATE DEFAULT '1999-12-31',"
        + " ModTime TIMESTAMP DEFAULT '2016-12-31 23:59:59.999')");
      System.out.println("Table created.");
      sta.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
    }
  }
}

When you run this example program for the first time, table Profile will be created. If you run it again, you will get an exception:

herong> java -cp %DERBY_HOME%\lib\derbyclient.jar DerbyIdentityColumn.java
herong> java -cp $DERBY_HOME/lib/derbyclient.jar DerbyIdentityColumn.java

Table created.

herong> java -cp %DERBY_HOME%\lib\derbyclient.jar DerbyIdentityColumn.java
herong> java -cp $DERBY_HOME/lib/derbyclient.jar DerbyIdentityColumn.java

Exception: Table/View 'PROFILE' already exists in Schema 'APP'.