CreateTable using changeset with auto_increment for oracle/postgresql/mysql

Liquibase Development
1

Hi Team,

I have a requirement of Create table with auto_increment using same changeset in Oracle, PostgreSQL and MySQL databases.

I have tried below approach…

  • CreateTable with defaultValueSequenceNext but this is not supported on MySQL database
  • Added changeset for “addAutoIncrement” but its not working with Oracle database.

What is common approach we can use, so it works for all Oracle, PostgreSQL and MySQL databases.

2

Hi @devang.makwana ,

First of all, Oracle does not support an auto increment / identity mechanism at column level. To achieve this you need to create a sequence (standalone Oracle object) and use this for the incremental ids. There’s a ton of documentation on how to create a sequence in Oracle.

Now back on your problem:
For MySQL and PostreSQL (these have auto increment/identity support):

  • I would either set this at column level, in the createTable changeset (in example changeset.id - 1). (see createTable | Liquibase Docs
    / column section / autoIncrement.
  • Or have the createTable only for the columns types and names and introduce a second changeset(after the createTable one) with (changeset.id - 2) just for the auto increment property: addAutoIncrement | Liquibase Docs
  • In either way, both changeset.id - 1 and changeset.id - 2 should have the changeset built in "dbms" tag on the changeset tag (in this case we should have use the built in "dbms" tag on the changeset tag (so that translates into: dbms=“PostgreSQL,MySQL”)).

For Oracle:

  • After the createTable changeset (in case you opt for adding the auto increment separately for the above / option B), add Oracle to the dbms tag as well (so the createTable / dbms=“PostgreSQL,MySQL, oracle”).
  • Add a second changeset for creating the sequence to be used for the auto increment functionality (in example changeset.id 2): createSequence | Liquibase Docs
    This second changeset has dbms=“oracle” only.

Hope this helps,
Eduard

1 Like
3

Hi @EduardUta,

Thanks for the solution I have used workaround as below and its working as expected.

  • Create simple table without any autoincrement clause for all databases
  • Added changeset “addAutoIncrement” for dbms=“postgreSQL,MySQL”
  • Added sql changeset to alter table modify default column for dbms=“oracle”

Mark Solved!

2 Likes
4

First of all, Oracle does not support an auto increment / identity mechanism at column level.

Wasn’t this statement incorrect even when it was written in 2021?
AFAIK, Oracle has supported this since version 12c.