SQL Power Software Forum

I have a hibernate generated H2 database. Two tables are connected via a third in a many-to-many relationship. The connection table has it's own primary key, which is not including any of the FK fields in the connection table. This is verified in the DDL generated by hibernate and by inspecting the connection table in a different database tool.

The connection table also has a value field called ROLE.

When the three tables are opened in Power Architect, ROLE and one of the FK fields are included in the PK.

Hibernate 3.6.1, Power Architect 1.0.6, java 1.6.0_26, Linux 2.6.38-12-generic (amd64) (Ubuntu 11.04), H2 database 1.3.153 (2011-03-14).

*Hibernate DDL:

create table T03INVESTMENT_ACCOUNT_ADDITIONAL_PARTY_ASSOCIATION (
ADDITIONAL_PARTY_ID bigint generated by default as identity,
ROLE varchar(25) not null,
INV_ACCOUNT_ID bigint not null,
INV_ACCOUNT_PARTY_ID bigint not null,
primary key (ADDITIONAL_PARTY_ID)

alter table T03INVESTMENT_ACCOUNT_ADDITIONAL_PARTY_ASSOCIATION
add constraint FKD34AAEF7B6A3C6C6
foreign key (INV_ACCOUNT_PARTY_ID)
references T03InvestmentAccountParty

alter table T03INVESTMENT_ACCOUNT_ADDITIONAL_PARTY_ASSOCIATION
add constraint FKD34AAEF7E45C1F6F
foreign key (INV_ACCOUNT_ID)
references T03InvestmentAccount

*SQL Power Architect DDL (for DB2) generated after reverse engineering the H2 database (And this is also what is shown in ER diagram and in the database explorer). Note definition of PK.

CREATE TABLE T03INVESTMENT_ACCOUNT_ADDITIONAL_PARTY_ASSOCIATION (
ADDITIONAL_PARTY_ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_80D5D060_7F1A_44C1_82C0_CEFE74EE462E) NOT NULL,
INV_ACCOUNT_PARTY_ID BIGINT NOT NULL,
ROLE VARCHAR(25) NOT NULL,
INV_ACCOUNT_ID BIGINT NOT NULL,
CONSTRAINT T03INVESTMENT_ACCOUNT_ADDITIONAL_PARTY_ASSOCIATION_pk PRIMARY KEY (ADDITIONAL_PARTY_ID, INV_ACCOUNT_PARTY_ID, ROLE)

;

....

Postgresql support a type called serial which is convenient for use as pk, it is automatically connected to a sequence, which is also automatically created.

When programming JPA/EJB3 the serial type comes in handy and is easy to set up and use with JPA.

My suggestion is to add support for Serial in Power Architect.