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) 2007 by Dr. Herong Yang. 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 '1988-12-31',"
+ " ModTime TIMESTAMP DEFAULT '2006-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 program, table Profile will be created. But you run it again, you will get an exception:
