foreign key not set correctly for composite-element

keting · **Joined:** Thu Jan 15, 2004 3:16 pm **Posts:** 10

Hi, I am having trouble with composit-element in an entity.

First are the mapping files:

BaseSecurity.hbm.xml

Code:

<hibernate-mapping>
   <class name="com.saghill.domainmodel.security.BaseSecurity" table="trading..security">
      <cache usage="read-write"/>

      <id name="Id" column="securityId" type="int">
         <generator class="native"/>
      </id>      
      
      <discriminator
         column="securityType"  
         type="string"      
      />
              
      <map name="tranches" table="trading..tranche" lazy="false" cascade="all-delete-orphan">
         <key column="security"/>
         <index column="tranche" type="integer"/>
         <composite-element class="com.saghill.domainmodel.security.Tranche">            
            <!--property name="type"          column="tranche"/-->
            <property name="bbid"          column="bbid"   type="string"/>
            <property name="common"        column="common" type="string"/>
            <property name="cusip"         column="cusip"  type="string"/>
            <property name="isin"          column="isin"   type="string"/>
            <property name="sedol"         column="sedol"  type="string"/>
            <!--parent name="security"/-->      
         </composite-element>
      </map>        
                                                                     
      
   </class>
</hibernate-mapping>

Tranche.hbm.xml

Code:

<hibernate-mapping>
   <class name="com.saghill.domainmodel.security.Tranche" table="trading..tranche">
      <cache usage="read-write"/>

      <composite-id>
         <key-property name="security"/>
         <key-property name="type" column="tranche"/>
      </composite-id>
      
      <property name="bbid"          column="bbid"   type="string"/>
      <property name="common"        column="common" type="string"/>
      <property name="cusip"         column="cusip"  type="string"/>
      <property name="isin"          column="isin"   type="string"/>
      <property name="sedol"         column="sedol"  type="string"/>      
   </class>
</hibernate-mapping>

As you can see, BaseSecurity is the owning entity and tranches are composite elements belonging to it. Tranche has a composite key, consisting of the security id and tranche type. This mapping works fine when I load an existing Security and add a tranche to it. Problem comes up when I want to create a new security with a few new tranches:

Code:

BaseSecurity security = new BaseSecurity();
Tranche tranche = new Tranche();
security.addTranche(tranche);
Session hibernate = null;
hibernate = getHBSession();
hibernate.saveOrUpdate(security);
hibernate.flush();
hibernate.connection().commit();

From the profiler, I see that Hibernate issues a select @@identity command and uses its result as foreign key for the new tranches. Instead, it should have used the primary of security that was just inserted.

Did I forget to specify the foreign key constraint through composite-element? Please help, thanks.[/list]

emmanuel · **Posted:** Thu Jan 15, 2004 7:36 pm

You cannot map a component as an entity. A component is dependant of it's master entity.

keting · **Joined:** Thu Jan 15, 2004 3:16 pm **Posts:** 10

thanks. I will remove the Tranche.hbm.xml file and report how it's working.

keting · **Joined:** Thu Jan 15, 2004 3:16 pm **Posts:** 10

Hi, Tranche.hbm.xml has been removed, however the new mappings exhibit the same problems.

This is the new mapping file:
BaseSecurity.hbm.xml

Code:

<hibernate-mapping>
   <class name="com.saghill.domainmodel.security.BaseSecurity" table="trading..security">
      <cache usage="read-write"/>

      <id name="Id" column="securityId" type="int">
         <generator class="native"/>
      </id>      
      
      <discriminator
         column="securityType"  
         type="string"      
      />
      <map name="tranches" table="trading..tranche" lazy="false" cascade="all-delete-orphan">
         <key column="security"/>
         <index column="tranche" type="integer"/>
         <composite-element class="com.saghill.domainmodel.security.Tranche">            
            <!--property name="type"          column="tranche"/-->
            <property name="bbid"          column="bbid"   type="string"/>
            <property name="common"        column="common" type="string"/>
            <property name="cusip"         column="cusip"  type="string"/>
            <property name="isin"          column="isin"   type="string"/>
            <property name="sedol"         column="sedol"  type="string"/>
            <!--parent name="security"/-->      
         </composite-element>
      </map>

   </class>
</hibernate-mapping>

SchemaGenerator gave me the following DDL, which looks right:

Code:

create table trading..security (
   securityId INT IDENTITY NOT NULL,
   securityType VARCHAR(255) not null,
   securityName VARCHAR(255) null,
   symbol VARCHAR(255) null,
   primary key (securityId)
);

create table trading..tranche (
   security INT not null,
   bbid VARCHAR(255) null,
   common VARCHAR(255) null,
   cusip VARCHAR(255) null,
   isin VARCHAR(255) null,
   sedol VARCHAR(255) null,
   tranche INT not null,
   primary key (security, tranche)
);

alter table trading..tranche add constraint FK74DBABD438927740 foreign key (security) references trading..security;

But at run-time, Hibernate does not seem to obey the foreign key constraint and instead uses the result of

Code:

select @@identity

to assign to new tranches :|

michael · **Posted:** Fri Jan 16, 2004 11:16 am

Could you post your inserting code and the appropriate hibernate log excerpt?

keting · **Joined:** Thu Jan 15, 2004 3:16 pm **Posts:** 10

Insertion code looks like the following:

Code:

Session hibernate = null;
hibernate = getHBSession();
hibernate.saveOrUpdate(security);
hibernate.flush();
hibernate.connection().commit();

error log:

Code:

10:34:57,851 ERROR JDBCExceptionReporter:46 - [TSING-TAO]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'securityId'. The conflict occurred in database 'Trading', table 'Security', column 'securityID'.
10:34:57,866 ERROR JDBCExceptionReporter:38 - could not insert collection: [com.saghill.domainmodel.security.BaseSecurity.tranches#2093692]
com.inet.tds.SQLException: Msg 547, Level 16, State 0, Line 1, Sqlstate 23000
[TSING-TAO]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'securityId'. The conflict occurred in database 'Trading', table 'Security', column 'securityID'.
   at com.inet.tds.a.a(Unknown Source)
   at com.inet.tds.a.a(Unknown Source)
   at com.inet.tds.c.int(Unknown Source)
   at com.inet.tds.c.executeUpdate(Unknown Source)
   at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
   at net.sf.hibernate.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:538)
   at net.sf.hibernate.impl.ScheduledCollectionRecreate.execute(ScheduledCollectionRecreate.java:23)
   at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2308)
   at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2265)
   at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2187)
   at com.saghill.domainmodel.security.util.SHSecurityFactory.saveInternal(SHSecurityFactory.java:59)

SQL log:

Code:

RPC:Completed   declare @P1 int
set @P1=1
exec sp_prepare @P1 output, N'@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 bit,@p5 bit,@p6 real,@p7 int,@p8 int,@p9 int,@p10 int,@p11 int', N'insert into trading..security (securityName, symbol, bbSymbol, active, settlesPhysically, priceFactor, currency, country, exchange, issuecompany, corporateentity, securityType) values (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, ''1'')'
select @P1

RPC:Completed   exec sp_execute 1, N'APE Test Stock01-16-04 10:34:46:046', N'TESTSTK139678', NULL, 1, 0, 1.000000000000000e+001, 1, 1, 1, 0, 4117   

RPC:Completed   declare @P1 int
set @P1=2
exec sp_prepare @P1 output, N'', N'select @@identity'
select @P1   

RPC:Completed   exec sp_execute 2   

RPC:Completed   declare @P1 int
set @P1=3
exec sp_prepare @P1 output, N'@p1 int,@p2 int,@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 nvarchar(4000)', N'insert into trading..tranche (security, tranche, bbid, common, cusip, isin, sedol) values (@p1, @p2, @p3, @p4, @p5, @p6, @p7)'
select @P1   
   
RPC:Completed   exec sp_execute 3, 2093692, 1, NULL, NULL, NULL, NULL, NULL

Please let me know if you need logging from specific hibernate packages.
Thanks!

michael · **Posted:** Fri Jan 16, 2004 11:56 am

Please set show_sql=true and post some more of the hibernate log. This is better to read.

keting · **Joined:** Thu Jan 15, 2004 3:16 pm **Posts:** 10

I did some more debugging, and found the cause of this problem.

When hibernate inserts the BaseSecurity object, it does not "read-back" the identity key that is now in the actual table. Instead hibernate relies on the identity key from "select @@identity" and use it as the foreign key for Tranche. To prove this is the case, I tried not to associate any Tranche's with BaseSecurity, there will be no SQL error, but the pk of new security (say 2900034) does not match what SQL server generated (20031). Hence any new Tranche with 2900034 would violate foreign key constraint. Anyone knows how to make Hibernate read back SQL server generated keys?

michael · **Posted:** Fri Jan 16, 2004 12:35 pm

Well, select @@identity should do exactly that, shouldn't it?

keting · **Joined:** Thu Jan 15, 2004 3:16 pm **Posts:** 10

That's not the case. SQL Serve seems the give out identity numbers per table. So if I have table A and B (both with identity PK), and examine their primary keys as a whole, the values would overlap. Table A might have 1,2,4,5... and B can have 0,1,2,5...20001...

michael · **Posted:** Fri Jan 16, 2004 12:44 pm

Please. show the output with show_sql = true

keting · **Joined:** Thu Jan 15, 2004 3:16 pm **Posts:** 10

here you go:

Code:

11:56:59,101 DEBUG SQL:223 - insert into trading..security (securityName, symbol, bbSymbol, active, settlesPhysically, priceFactor, currency, country, exchange, issuecompany, corporateentity, securityType) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '1')
11:56:59,179 DEBUG SQL:223 - select @@identity
11:57:01,320 DEBUG SQL:223 - insert into trading..tranche (security, tranche, bbid, common, cusip, isin, sedol) values (?, ?, ?, ?, ?, ?, ?)
11:57:04,758  WARN JDBCExceptionReporter:38 - SQL Error: 547, SQLState: 23000
11:57:04,758 ERROR JDBCExceptionReporter:46 - [TSING-TAO]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'securityId'. The conflict occurred in database 'Trading', table 'Security', column 'securityID'.
11:57:04,773 ERROR JDBCExceptionReporter:38 - could not insert collection: [com.saghill.domainmodel.security.BaseSecurity.tranches#2093700]
com.inet.tds.SQLException: Msg 547, Level 16, State 0, Line 1, Sqlstate 23000
[TSING-TAO]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'securityId'. The conflict occurred in database 'Trading', table 'Security', column 'securityID'.
   at com.inet.tds.a.a(Unknown Source)
   at com.inet.tds.a.a(Unknown Source)
   at com.inet.tds.c.int(Unknown Source)
   at com.inet.tds.c.executeUpdate(Unknown Source)
   at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
   at net.sf.hibernate.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:538)
   at net.sf.hibernate.impl.ScheduledCollectionRecreate.execute(ScheduledCollectionRecreate.java:23)
   at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2308)
   at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2265)
   at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2187)

Thanks.

michael · **Posted:** Fri Jan 16, 2004 1:02 pm

Well, I can't see why this should not work:

insert into trading..security <- generates securityId
select @@identity <- returns the generated id
insert into trading..tranche <- reuses the generated id for the security column

Are you sure your select @@identity is not somehow messed up?

keting · **Joined:** Thu Jan 15, 2004 3:16 pm **Posts:** 10

your suspicion is right, we have triggers on Security table, which inserts into a second table with identity column. So the identity value of Security was overriden. To achieve what we what to do, we need to use:
select SCOPE_IDENTITY()

Is there an elegant way of telling Hibernate to use this function, rather than patching the SQLServerDialect?

michael · **Posted:** Fri Jan 16, 2004 1:39 pm

Just extend SqlServerDialect and override getIdentitySelectString()