Auto-generated primary key when column has been defined as GENERATED BY DEFAULT AS IDENTITY

[GoogleCodeExporter]

Example:

CREATE TABLE societies (
    id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
    code SMALLINT NOT NULL, 
    name VARCHAR(250) NOT NULL);

ALTER TABLE societies ADD CONSTRAINT pk_societies PRIMARY KEY (id);

The second sentence fails because a primary key already has been defined for 
the table (Error code 90017).

I am using version 1.3.162 of H2.

I think that there was a similar bug long time ago. The bug was that when a 
table was created, if some of the columns had an AUTO_INCREMENT indicator, then 
a primary key will be automatically created for that column.

I want to use H2 as a database for junit testing and development, but the 
production database is Sybase ASE. In Sybase, only one column per table can be 
declared as identity, but for such column is not mandatory to be the primary 
key. Therefore, the primary key must be always declared explicitly.

If this is not possible for all databases, maybe it will fit in a Sybase or 
SQLServer compatibility mode (I currently use the SQLServer mode because SQL 
Server is similar to Sybase in quite aspects).

Original issue reported on code.google.com by jesus.ca...@gmail.com on 7 Dec 2011 at 6:00

[GoogleCodeExporter]

Hi,

Could you use AUTOINCREMENT instead of IDENTITY? See also: 
http://h2database.com/html/grammar.html#column_definition

"Identity and auto-increment columns are columns with a sequence as the 
default. The column declared as the identity columns is implicitly the primary 
key column of this table (unlike auto-increment columns)."

Original comment by thomas.t...@gmail.com on 15 Dec 2011 at 10:04

• Changed state: WontFix

[GoogleCodeExporter]

Hi,
you are right. But I cannot change the DDL because it was created
automatically by Liquibase. Maybe I should report this issue to them.

Anyway, I solved my problem adding the primary key directly from the
"CREATE TABLE" sentence, instead of adding a constraint later.

Many thanks for your support.

Original comment by jesus.ca...@gmail.com on 15 Dec 2011 at 11:32