hibernate schema generator adds index twice?

Lasek · **Joined:** Fri Apr 29, 2005 10:57 pm **Posts:** 41

Hibernate version: 3.0.2

Basically, in a table which's a many-to-many bridge, I have the following SQL generated by SchemaExport:

Code:

create table accounts_privileges_bridge (
   accountID bigint not null,
   privilegeID bigint not null,
   primary key (accountID, privilegeID)
)

# cool so far

# now the ones I'm concerned about
alter table accounts_privileges_bridge add index FKE93B366D1EEAC239 (accountID), add constraint FKE93B366D1EEAC239 foreign key (accountID) references accounts (ID)
alter table accounts_privileges_bridge add index FKE93B366DACC5170C (privilegeID), add constraint FKE93B366DACC5170C foreign key (privilegeID) references account_privileges (ID)

so basically, the alter table statements try to add another index onto those keys, with the exception that this time they specify "foreign key".

Is this the way it's meant to be? having 4 indexes in that table of 2 rows?

meph · **Joined:** Tue Nov 15, 2005 10:05 am **Posts:** 9

That looks perfectly fine to me, the primary key constraints plus constraints that the FKs must match PKs on the other tables.

But what bugs me is how you got the 'primary key' constraint on the link table. I've spent ages trying add something to a many-to-many mapping to put a uniqueness constraint on the link table.

Can you tell me what you did?
Thanks
Mike

esword · **Joined:** Tue Jun 07, 2005 11:36 pm **Posts:** 22

Should be done automatically, assuming you've indicated a key column in your association mapping. Here's an example from my project. This represents the "results" property of a QuerySummary object:

Code:

    <set table="Query_Result" lazy="true" name="results">
      <key column="queryId"/>
      <many-to-many column="itemId" class="com.osc.QueryTreeNG.datasource.DataSourceItem"/>
    </set>

And the generated sql for the results table is:

Code:

create table Query_Result (
    queryId bigint not null,
    itemId varchar(255) not null,
    primary key (queryId, itemId)
);

The id column of the DataSourceItem (which is what the itemId column maps to) is a varchar rather than a standard Long in case you were wondering...

meph · **Joined:** Tue Nov 15, 2005 10:05 am **Posts:** 9

I guess you're lucky. I've found that this (in a class called Connection):

<bag name="Hops" table="Connection_HopLink" lazy="true" batch-size="25">
<key>
<column name="Connections" index="IDX_Cnnctn_HpLnk_Cnnctns"/>
</key>
<many-to-many class="com.nexagent.nboss.model.hibernate.pojo.Hop" column="Hops" foreign-key="FK_Connection_Hops"/>
</bag>

And this in the Hop class:

<bag name="Connections" table="Connection_HopLink" inverse="true" lazy="true" batch-size="25">
<key>
<column name="Hops" index="IDX_Connection_HopLink_Hops"/>
</key>
<many-to-many class="com.nexagent.nboss.model.hibernate.pojo.Connection" column="Connections" foreign-key="FK_Hop_Connections"/>
</bag>

Generates this:

create table Connection_HopLink (Connections number(19,0) not null, Hops number(19,0) not null);
create index IDX_Cnnctn_HpLnk_Cnnctns on Connection_HopLink (Connections);
create index IDX_Connection_HopLink_Hops on Connection_HopLink (Hops);
alter table Connection_HopLink add constraint FK_Hop_Connections foreign key (Connections) references Connection;
alter table Connection_HopLink add constraint FK_Connection_Hops foreign key (Hops) references Hop;

Tried removing the indexes and FK names and it still doesn't generate the primary key. I'm using Hibernate 3.0 with the Oracle9Dialect. Maybe there's a global setting somewhere or maybe it's because I'm using <bag>.

Thanks anyway :-)
Mike

meph · **Joined:** Tue Nov 15, 2005 10:05 am **Posts:** 9

We've been using 'bag' instead of 'set' for performance reasons. This optimisation was done before my time.
Because we really mean 'set' I wanted the primary key constraint, but since a 'bag' allows the same item in the collection multiple times I can't have it.

All sown to my confusion. Thanks for your help esword.