Derby AutoIncrement

General Discussion
1

Hi,

I’m trying to implement Liquibase with a derby database, and i’m running into a problem with our JPA inserts. Liquibase seems to produce the following SQL:

CREATE TABLE employee (id INT GENERATED BY DEFAULT AS IDENTITY  NOT NULL, name VARCHAR(50) NOT NULL, CONSTRAINT PK_EMPLOYEE PRIMARY KEY (id));

What I’m looking for this:

CREATE TABLE employee (id INT GENERATED ALWAYS AS IDENTITY  NOT NULL, name VARCHAR(50) NOT NULL, CONSTRAINT PK_EMPLOYEE PRIMARY KEY (id));

Is there anyway to do this with liquibase? I’m using the 2.0 RC1 due to issues with the 1.9.5.0 release.

Thanks!

2

What is the problem you are running into.  The only difference I thought was that ALWAYS did not allow you to specify an ID, even if you wanted to.  For more flexibility I thought the BY DEFAULT option was better.  If there are things that break due to BY DEFAULT I could change it if it made sense.

Since you are on 2.0, you can use the new plug-in system.  You should be able to create a new class in a subpackage of “liquibase.ext” and extend liquibase.database.core.DerbyDatabase.  Override getPriority and return a value higher than 10 and override the getAutoIncrementClause() method to return “GENERATED ALWAYS AS IDENTITY”

Nathan

3

Thanks for the heads-up on the ext mechanism, I’ll see if I can make that work.

The problem I’m having is I have JPA annotated objects defined with integers as primary keys, and they always have a value, which is causing problems when inserting our seed data into (or anything else for that matter) into the tables.

4

I would have thought JPA would know that the id has the default value and that it should still insert without passing it.  Interesting. Thanks for the heads up.  I’ll think about what seems like the best default.

Nathan