Autogenerated key with Oracle

Each database, especially the aged and commercial once have a bunch of pitfalls. To avoid issues with these database, see our summary.

1. Autogenerated primary key

Up to Oracle 12c (year 2013) there was no good option to handle the primary key via a autogenerated ID. You had to use either a sequence or your had to install a trigger which itself uses the sequence. Luckily Oracle introduced the IDENTITY column type to create primary keys with autogenerated IDs, to ensure that independently what a developer inserts the ID columen will be unique and autogenerated.

1.1. How to use the IDENTITY column?

It is similar to other databases, just define your id column as IDENTITY and you are done.

CREATE TABLE autogeneratedIdTable (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  description VARCHAR2(100) NOT NULL
);

How to define it in Liquibase?

<changeSet id="addAutoIncrement-example">
   <addAutoIncrement columnDataType="int" columnName="id" generationType="ALWAYS" incrementBy="1" tableName="autogeneratedIdTable" />
</changeset>