Compound PK with identity field

[Tony Nelson]

I'm trying to make a small table that has a compound PK that includes an identity field like this

CREATE TABLE agreement4 (
   agreementid identity  NOT NULL,
   agreementtypeid int4 NOT NULL,
   version VARCHAR(6) NOT NULL,
   companyid int4 NOT NULL,
   userid int4 NOT NULL,
   date TIMESTAMP NOT NULL,
CONSTRAINT PK_AGREEMENT4 PRIMARY KEY (agreementid, agreementtypeid, version))

This fails with a duplicate primary key exception.  Changing agreement id to int4 makes the table creation succeed.

Any help would be greatly appreciated.

[Rami Ojares]

Why would you want to make a superkey to primarykey?

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/H4zEzI9p230J.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>

[Tony Nelson]

It's in a DB that our system has had forever.  It turns out that I can remove the compound PK and just create a unique index on the other attributes.

I think that was the original intent.

At any rate, I know that this type of Compound PK was supported on MSSQL (original db) and Postgres (current db).  Whether it makes a lick of sense or not I leave for the SQL theorists. 

Thanks
Tony

[Thomas Mueller]

Hi,

> Compound PK was supported on MSSQL
> (original db) and Postgres (current db).

This is supported by H2 as well. That's not the problem. The problem
is you have used the keyword "identity" which for H2 means this column
is automatically the primary key, as documented:
http://h2database.com/html/grammar.html#column_definition

What you actually want is "auto-increment":

CREATE TABLE agreement4 (
agreementid bigint auto_increment not null,

agreementtypeid int4 NOT NULL,
version VARCHAR(6) NOT NULL,
companyid int4 NOT NULL,
userid int4 NOT NULL,
date TIMESTAMP NOT NULL,
CONSTRAINT PK_AGREEMENT4 PRIMARY KEY (agreementid, agreementtypeid, version))

This works.

Regards,
Thomas