Liquibase createTable with autoIncrement column generates different SQL for Oracle 11g and 12c

Question

This is my first question so please be gentle ;-).

We have a liquibase project with the following changeSet:

<changeSet author="Me" id="db.changelog-master-1">
  <preConditions onFail="MARK_RAN">
    <not>
      <tableExists tableName="table_x" />
    </not>
  </preConditions>
  <createTable remarks="Groep" tableName="table_x">
    <column autoIncrement="true" name="id" type="${serial}">
      <constraints primaryKey="true" primaryKeyName="table_x_pk" />
    </column>
    <column name="indsync" type="varchar(5)">
      <constraints nullable="false" />
    </column>
    <column name="code" type="varchar(10)" />
    <column defaultValue="2" name="code_n" type="char(1)">
      <constraints nullable="false" />
    </column>
    <column name="description" type="varchar(80)" />
    <column defaultValue="2" name="description_n" type="char(1)">
      <constraints nullable="false" />
    </column>
  </createTable>

When starting our application that uses Oracle 11g, the created table looks like this:

  CREATE TABLE "SOMETHING"."TABLE_X" 
   (  "ID" NUMBER(*,0) NOT NULL ENABLE, 
   "INDSYNC" VARCHAR2(5 CHAR) NOT NULL ENABLE, 
   "CODE" VARCHAR2(10 CHAR), 
   "CODE_N" CHAR(1 CHAR) DEFAULT '2' NOT NULL ENABLE, 
   "DESCRIPTION" VARCHAR2(80 CHAR), 
   "DESCRIPTION_N" CHAR(1 CHAR) DEFAULT '2' NOT NULL ENABLE, 
    CONSTRAINT "TABLE_X_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
  TABLESPACE "SOMETHING"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
  TABLESPACE "SOMETHING" ;

   COMMENT ON TABLE "SOMETHING"."TABLE_X"  IS 'Groep';

  CREATE OR REPLACE TRIGGER "SOMETHING"."TABLE_X_BI" before insert on table_x for each row  WHEN (new.id is null) begin select table_x_seq.nextval into :new.id from dual; end;
/
ALTER TRIGGER "SOMETHING"."TABLE_X_BI" ENABLE;

This works as expected and the sequence.nextval is inserted in the id column. But when starting our application that uses Oracle 12c, the created table looks like this:

 CREATE TABLE "SOMETHING"."TABLE_X" 
   (  "ID" NUMBER(*,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE, 
   "INDSYNC" VARCHAR2(5 CHAR) NOT NULL ENABLE, 
   "CODE" VARCHAR2(10 CHAR), 
   "CODE_N" CHAR(1 CHAR) DEFAULT '2' NOT NULL ENABLE, 
   "DESCRIPTION" VARCHAR2(80 CHAR), 
   "DESCRIPTION_N" CHAR(1 CHAR) DEFAULT '2' NOT NULL ENABLE, 
    CONSTRAINT "TABLE_X_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOMETHING"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOMETHING" ;

   COMMENT ON TABLE "SOMETHING"."TABLE_X"  IS 'Groep';

  CREATE OR REPLACE EDITIONABLE TRIGGER "SOMETHING"."TABLE_X_BI" before insert on table_x for each row  WHEN (new.id is null) begin select table_x_seq.nextval into :new.id from dual; end;
/
ALTER TRIGGER "SOMETHING"."TABLE_X_BI" ENABLE;

The problem with the 12c created table is that the value of the sequence is never used for column ID since it won't be null, due to "GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE"

The id field is increments at every insert, but the sequence isn't used.

My problem is that after some time and a couple of changeSet's later, we wipe the content of table_x , drop the sequence and recreate it to start over. This works perfectly using oracle 11g, but not in 12c. The id field in 12c keeps on incrementing and doesn't get reset, which causes problems while starting our application.

What do i need to do, to have the same createTable definition on oracle 12c as we have on 11g?

Grtz,

Lex

EDIT 1: The liquibase version we use is 3.4.2 (maven dependency org.liquibase:liquibase-core)

EDIT 2: As requested by @SteveDonie, the ${serial} is defined as follows:

<property name="serial" value="numeric(*,0)" dbms="oracle"/>
<property name="serial" value="serial" dbms="postgresql"/>
<property name="serial" value="int(10) UNSIGNED" dbms="mysql"/>

For Oracle there is only one definition which is used for both versions.

Your changeset mentions a parameter ${serial} - is that being set differently for the two platforms? — SteveDonie
Hi @SteveDonie, I modified the question with the requested info. — Lex Uijthof
I've read [here][1] that there where changes in 12c regarding the identity column, but it also states that the old-style trigger-based solution still works, but perform's less. [1]: oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1 — Lex Uijthof

Levente Takács Levente Takács · Accepted Answer · 2019-09-23T13:08:53

Have you tried the defaultValueComputed tag for the ID field?

<changeSet id="table_x_seq_create" author="takacsl">
    <createSequence cacheSize="2000" cycle="false" incrementBy="1" sequenceName="SEQ_TABLE_X" startValue="1"/>
</changeSet>
<changeSet id="table_x_create" author="takacsl">
  <createTable remarks="Groep" tableName="table_x">
    <column name="id" type="integer" defaultValueComputed="nextval('SEQ_TABLE_X')>
      <constraints primaryKey="true"  />
    </column>
    <column name="indsync" type="varchar(5)">
      <constraints nullable="false" />
    </column>
    <column name="code" type="varchar(10)" />
    <column defaultValue="2" name="code_n" type="char(1)">
      <constraints nullable="false" />
    </column>
    <column name="description" type="varchar(80)" />
    <column defaultValue="2" name="description_n" type="char(1)">
      <constraints nullable="false" />
    </column>
  </createTable>
</changeSet>

Another - much more complex - workaround is to overwrite sql code generation with a custom builder class.

Liquibase createTable with autoIncrement column generates different SQL for Oracle 11g and 12c

1 Answers