March 20, 2013

How to make primary key column with auto increment in HSQLDB TEXT Table

IDENTITY keyword can be use to make the table column both as primary key and auto increment.
An IDENTITY column is always treated as the primary key for the table (as a result, multi-column primary keys are not possible with an IDENTITY column present).
For example:


CREATE TEXT TABLE test (
  test_ID INTEGER IDENTITY,
  test_VERSION varchar(10) NOT NULL 
) ;

Insert into test (test_version) values (`5`);

If you run the above code, it will automatically insert incremented value in test_ID column with given test_version column, as follows;

test_ID  test_version
1  5

3 comments:

  1. identity starts with 0 how to make it start from 1 ?

    ReplyDelete
    Replies
    1. CREATE TABLE users (
      id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY KEY,
      name VARCHAR(30),
      email VARCHAR(50)
      );

      Delete