Foreign Key Constraint Violation

eqbridges · **Posted:** Thu Feb 02, 2006 5:06 pm

This is happening when I'm trying to store an instance of ScenarioAnalysisResultImpl. This class is mapped many-to-one to a PortfolioResultImpl, which gets assigned an identifier by hibernate (in the example below, it's 101).

The complete log is available here:
http://www.accidental.org/output.log.gz

The table definition for the two tables (including the constraint definition), are appended at the end of this post.

Hibernate version: 3.1.2

Name and version of the database you are using: Sybase ASE 12.5

Mapping documents:

Code:

<hibernate-mapping package="citco.esd.risk.scenario.domain.result">
    <class name="ScenarioAnalysisResultsImpl" table="risk.dbo.execution_log">
        <id name="executionId" column="execution_id" type="long">
            <generator class="hilo">
                <param name="table">risk.dbo.hibernate_unique_key</param>
                <param name="column">next_hi</param>
                <param name="max_lo">100</param>
            </generator>
        </id>
        <version name="version"/>
        <many-to-one class="citco.esd.risk.scenario.domain.config.PortfolioResultImpl" name="portfolioResult"
                     column="res_portfolio_id" not-null="true" cascade="save-update"/>

        <property name="valueDate" column="value_date" type="date" not-null="true"/>
        <property name="executionStart" column="execution_start" type="timestamp" not-null="true"/>
        <property name="executionEnd" column="execution_end" type="timestamp" not-null="true"/>
        <property name="executionStatus" column="execution_status" type="citco.esd.risk.scenario.domain.result.ExecutionStatus" not-null="true"/>
        <property name="executingUser" column="executing_user" type="string" not-null="true"/>
        <property name="executionOwner" column="execution_owner" type="int" not-null="false"/>
        <bag name="results" cascade="all,delete-orphan">
            <key column="execution_id"/>
            <one-to-many class="citco.esd.risk.scenario.domain.result.Result"/>
        </bag>
    </class>
</hibernate-mapping>

Code:

<hibernate-mapping
    package="citco.esd.risk.scenario.domain.config">
  <class name="PortfolioResultImpl" table="risk.dbo.res_portfolio">
    <id name="portfolioResultId" column="res_portfolio_id" type="long">
      <generator class="hilo">
   <param name="table">risk.dbo.hibernate_unique_key</param>
   <param name="column">next_hi</param>
   <param name="max_lo">100</param>
      </generator>
    </id>
    <version name="version"/>
    <property name="portfolioId" column="portfolio_id" type="long" not-null="true"/>
    <property name="name" type="string" unique="true" not-null="true"/>
    <property name="description" type="string" not-null="false"/>
    <property
   name="owningUser"
   column="owning_user"
   type="integer"
   not-null="false"/>
    <property name="auditInfo" type="citco.esd.risk.scenario.domain.util.AuditInfoType" not-null="true">
      <column name="create_date" not-null="true"/>
      <column name="create_user" not-null="true"/>
      <column name="modify_date" not-null="true"/>
      <column name="modify_user" not-null="true"/>
    </property>
    <set name="fundIds" table="risk.dbo.res_portfolio_funds" cascade="all">
       <key column="res_portfolio_id"/>
       <element column="fund_id" type="integer"/>
    </set>
    <set name="strategyIds" table="risk.dbo.res_portfolio_strategy" cascade="all">
       <key column="res_portfolio_id"/>
       <element column="strategy_id" type="integer"/>
    </set>
    <set name="traderIds" table="risk.dbo.res_portfolio_traders" cascade="all">
       <key column="res_portfolio_id"/>
       <element column="trader_id" type="integer"/>
    </set>
    <set name="instrumentTypeIds" table="risk.dbo.res_portfolio_instr_types" cascade="all">
       <key column="res_portfolio_id"/>
       <element column="instrument_type_id" type="integer"/>
    </set>
      <set name="issuerIds" table="risk.dbo.res_portfolio_issuer">
          <key column="res_portfolio_id"/>
          <element column="issuer_id" type="integer"/>
      </set>
      <set name="sectorIds" table="risk.dbo.res_portfolio_sector">
          <key column="res_portfolio_id"/>
          <element column="sector_id" type="integer"/>
      </set>
    <set name="scenarioGroups" table="risk.dbo.res_scenario_portfolio" cascade="all">
        <key column="res_portfolio_id"/>
        <many-to-many column="res_scenario_group_id" class="ScenarioGroupResultImpl"/>
    </set>
    <bag name="executionResults" cascade="all,delete-orphan">
        <key column="res_portfolio_id"/>
        <one-to-many class="citco.esd.risk.scenario.domain.result.ScenarioAnalysisResultsImpl"/>
    </bag>
  </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

    protected void storeResults(List resultList) {
        if(null != resultList && !resultList.isEmpty()) {
            if (log.isDebugEnabled()) {
                log.debug("Storing ["+resultList.size()+"] execution resultList.");
            }
            Session s = HibernateUtil.sessionFactory.openSession(new AuditInterceptor(Constants.SCENARIO_EXECUTION_USER));
            try {
                Transaction tx = s.beginTransaction();
                Iterator i = resultList.iterator();
                while(i.hasNext()) {
                    ScenarioAnalysisResults results = (ScenarioAnalysisResults) i.next();
                    s.saveOrUpdate(results);
                }
                tx.commit();
            } finally {
                s.close();
            }
    }

Full stack trace of any exception that occurs:

Code:

com.sybase.jdbc2.jdbc.SybSQLException: Foreign key constraint violation occurred, dbname =  'risk', table name = 'risk.dbo.execution_log', constraint name = 'fk_execution_log2'.
     at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2535)
     at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1916)
     at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
     at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:201)
     at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:182)
     at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1540)
     at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1523)
     at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:89)
     at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:23)
     at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2062)
     at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2427)
     at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)
     at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:243)
     at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:227)
     at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
     at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:296)
     at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
     at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1009)
     at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:356)
     at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
     at citco.esd.risk.scenario.main.AbstractExecutionContext.storeResults(AbstractExecutionContext.java:135)
     at citco.esd.risk.scenario.main.DefaultExecutionContext.run(DefaultExecutionContext.java:78)
     at citco.esd.risk.scenario.main.BlockingExecutionContext.run(BlockingExecutionContext.java:43)
     at java.lang.Thread.run(Thread.java:534)

The generated SQL (show_sql=true):

Code:

insert into risk.dbo.execution_log (version, res_portfolio_id, value_date, execution_start, execution_end, execution_status, executing_user, execution_owner, execution_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)

Debug level Hibernate log excerpt:

Code:

[15:19:23] DEBUG AbstractBatcher - preparing statement
[15:19:23] DEBUG AbstractEntityPersister - Dehydrating entity: [citco.esd.risk.scenario.domain.result.ScenarioAnalysisResultsImpl#606]
[15:19:23] DEBUG IntegerType - binding '0' to parameter: 1
[15:19:23] DEBUG LongType - binding '101' to parameter: 2
[15:19:23] DEBUG DateType - binding '05 December 2005' to parameter: 3
[15:19:23] DEBUG TimestampType - binding '15:19:08' to parameter: 4
[15:19:23] DEBUG TimestampType - binding '15:19:12' to parameter: 5
[15:19:23] DEBUG StringType - binding 'BlockingExecutionContext' to parameter: 7
[15:19:23] DEBUG IntegerType - binding null to parameter: 8
[15:19:23] DEBUG LongType - binding '606' to parameter: 9
[15:19:23] DEBUG AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[15:19:23] DEBUG AbstractBatcher - closing statement
[15:19:23] DEBUG JDBCExceptionReporter - could not insert: [citco.esd.risk.scenario.domain.result.ScenarioAnalysisResultsImpl] [insert into risk.dbo.execution_log (version, res_portfolio_id, value_date, execution_start, execution_end, execution_status, executing_user, execution_owner, execution_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)]
com.sybase.jdbc2.jdbc.SybSQLException: Foreign key constraint violation occurred, dbname =  'risk', table name = 'risk.dbo.execution_log', constraint name = 'fk_execution_log2'.
....stack trace removed....
[15:19:23] WARN  JDBCExceptionReporter - SQL Error: 546, SQLState: 23000
[15:19:23] ERROR JDBCExceptionReporter - Foreign key constraint violation occurred, dbname =  'risk', table name = 'risk.dbo.execution_log', constraint name = 'fk_execution_log2'.
[15:19:23] WARN  JDBCExceptionReporter - SQL Error: 3621, SQLState: 01ZZZ
[15:19:23] ERROR JDBCExceptionReporter - Command has been aborted.
[15:19:23] ERROR AbstractFlushingEventListener - Could not synchronize database state with session

Code:

CREATE TABLE execution_log (
    execution_id   INT NOT NULL,
    value_date     DATETIME NOT NULL,
    executing_user VARCHAR(64) NOT NULL,
    execution_status VARCHAR(64) NOT NULL,
    execution_start DATETIME NOT NULL,
    execution_end  DATETIME NOT NULL,
    execution_owner SMALLINT NULL,
    res_portfolio_id INT NULL,
    version        INT DEFAULT 0 NOT NULL,
CONSTRAINT pk_execution_log PRIMARY KEY CLUSTERED (execution_id),
CONSTRAINT fk_execution_log FOREIGN KEY (execution_owner)
    REFERENCES common..tudor_user (tudor_user_id),
CONSTRAINT fk_execution_log2 FOREIGN KEY (res_portfolio_id)
    REFERENCES res_portfolio (res_portfolio_id))
GO

Code:

CREATE TABLE res_portfolio (
    res_portfolio_id INT NOT NULL,
    portfolio_id   INT NOT NULL,
    name           VARCHAR(64) NOT NULL,
    description    VARCHAR(256) NULL,
    version        INT DEFAULT 0 NOT NULL,
    owning_user    SMALLINT NULL,
    create_date    DATETIME NOT NULL,
    create_user    SMALLINT NOT NULL,
    modify_date    DATETIME NOT NULL,
    modify_user    SMALLINT NOT NULL,
CONSTRAINT pk_res_portfolio PRIMARY KEY CLUSTERED (res_portfolio_id),
CONSTRAINT fk_res_portfolio FOREIGN KEY (owning_user)
    REFERENCES common..tudor_user (tudor_user_id),
CONSTRAINT fk_res_portfolio2 FOREIGN KEY (create_user)
    REFERENCES common..tudor_user (tudor_user_id),
CONSTRAINT fk_res_portfolio3 FOREIGN KEY (modify_user)
    REFERENCES common..tudor_user (tudor_user_id))
GO

eqbridges · **Posted:** Thu Feb 02, 2006 5:24 pm

I get the same error when inverse='true' is added, to wit:

Code:

    <bag name="executionResults" cascade="all,delete-orphan" inverse="true">
            <key column="res_portfolio_id"/>
            <one-to-many class="citco.esd.risk.scenario.domain.result.ScenarioAnalysisResultsImpl"/>
    </bag>

LaLiLuna · **Posted:** Thu Feb 02, 2006 5:38 pm

You are saving your many side, so I would not think that the changes you made will affect anything. You should keept the inverse anyway.

Try to set cascade to all on the ScenarioAnalysisResult.
Regards Sebastian

eqbridges · **Posted:** Thu Feb 02, 2006 6:10 pm

Okay, so I tried changing the storeResults method to look like the below. As well, added cascade='all' to ScenarioAnalysisResult, both to no avail -- still get the FK constraint violation.

Code:

    protected void storeResults(List resultList) {
        if(null != resultList && !resultList.isEmpty()) {
            if (log.isDebugEnabled()) {
                log.debug("Storing ["+resultList.size()+"] execution resultList.");
            }
            Session s = HibernateUtil.sessionFactory.openSession(new AuditInterceptor(Constants.SCENARIO_EXECUTION_USER));
            try {
                Transaction tx = s.beginTransaction();
                Iterator i = resultList.iterator();
                while(i.hasNext()) {
                    ScenarioAnalysisResults results = (ScenarioAnalysisResults) i.next();

                    // changed to store result as a member of the portfolio's list of results.
                    PortfolioResult pfolio = results.getPortfolioResult();
                    pfolio.getExecutionResults().add(results);

                    // then to save the portfolio
                    s.saveOrUpdate(pfolio);
                }
                tx.commit();
            } finally {
                s.close();
            }
      }