HSQLDB 2.0 and DDL containing IDENTITY

[Andy Jefferson]

Hi,
the following DDL works on HSQLDB 1.8 yet not on 2.0
CREATE TABLE IDENTITYGENERATORITEMNOFIELD
(
    IDENTITYGENERATORITEMNOFIELD_ID BIGINT NOT NULL IDENTITY,
    PRIMARY KEY (IDENTITYGENERATORITEMNOFIELD_ID)
)

HSQLDB 2.0 gives
java.sql.SQLException: primary key already exist
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)

presumably saying that the IDENTITY column is deemed the PK so the PRIMARY KEY part of the DDL is superfluous. Yet backwards compat is broken with that and I see no real problem in allowing the user to specify that, just pass it through if the same column is already the PK.

Thoughts ?

PS. thanks for all of the effort that has clearly gone into HSQLDB 2.0

[Fred Toussi]

It is difficult to provide full backward compatibility with quirks of earlier versions.

IDENTITY on its own is translated to GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

You can make the definition compatible with both 1.8 and 2.0

CREATE TABLE IDENTITYGENERATORITEMNOFIELD ( IDENTITYGENERATORITEMNOFIELD_ID BIGINT GENERATED BY DEFAULT AS IDENTITY, PRIMARY KEY (IDENTITYGENERATORITEMNOFIELD_ID) )

[Andy Jefferson]

Thx. Works for me in both versions