Formatted sql identity auto increment syntax?

1

The docs could use an update to include an example of an autoincremented identity column.

e.g. this works in postgres and h2 but not mysql:

create table Something (
    id bigint generated always as identity primary key
)

In mysql, you need to do:

create table Something (
    id bigint not null auto_increment primary key
)

What’s the proper syntax so that liquibase generates the proper identity column for all databases?

2

Hello, @kenkyee - Welcome to the liquibase community!

There is no auto-increment SQL that’s valid for all databases. They are all special in their own ways. Your best options with formatted SQL are probably:

  • Define a changelog parameter of ${autoIncrementClause} or something that you can put into your formatted SQL and pass along the needed value
  • Use the DBMS attribute and have separate versions of the changeset for each database you need to support.
  • Find a way of specifying that it’s compatible with the specific databases you’re using. Sometimes there are some overlapping compatibility syntax

Beyond those, one of the big advantages of XML/YML/JSON changelogs is that we allow them to say “make this column autoincrement” and we figure out the database-specific SQL that can handle that.