-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 
  Previous topic | Next topic 
Author Message
bbchops
 Post subject: Foreign key constraint violation in cascaded many to many
PostPosted: Tue Sep 27, 2005 10:19 am 
Newbie

Joined: Thu Jun 16, 2005 3:52 pm
Posts: 1
Hibernate version:
3.0.5

Mapping documents:
Company.hbm.xml:

Code:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
  <class table="companies" name="test.Company">
    <id unsaved-value="null" name="id">
      <generator class="native"/>
    </id>
    <property name="name"/>
    <set inverse="true" name="employees">
      <key column="employerId"/>
      <one-to-many class="test.Person"/>
    </set>
    <set inverse="true" table="formerEmployments" name="formerEmployees">
      <key column="companyId"/>
      <many-to-many class="test.Employment" column="employmentId"/>
    </set>
  </class>
</hibernate-mapping>


Person.hbm.xml:

Code:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
  <class table="companies" name="test.Company">
    <id unsaved-value="null" name="id">
      <generator class="native"/>
    </id>
    <property name="name"/>
    <set inverse="true" name="employees">
      <key column="employerId"/>
      <one-to-many class="test.Person"/>
    </set>
    <set inverse="true" table="formerEmployments" name="formerEmployees">
      <key column="companyId"/>
      <many-to-many class="test.Employment" column="employmentId"/>
    </set>
  </class>
</hibernate-mapping>


Employment.hbm.xml:

Code:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
  <class table="employments" name="test.Employment">
    <id unsaved-value="null" name="id">
      <generator class="native"/>
    </id>
    <property name="startDate"/>
    <property name="endDate"/>
  </class>
</hibernate-mapping>


schema.ddl:

Code:
alter table formerEmployments drop foreign key FK423588D65DE790EF;
alter table formerEmployments drop foreign key FK423588D67C86A98C;
alter table formerEmployments drop foreign key FK423588D618D99559;
alter table people drop foreign key FKC4E2328F7713A817;
drop table if exists companies;
drop table if exists employments;
drop table if exists formerEmployments;
drop table if exists people;
create table companies (
    id bigint not null auto_increment,
    name varchar(255),
    primary key (id)
);
create table employments (
    id bigint not null auto_increment,
    startDate datetime,
    endDate datetime,
    primary key (id)
);
create table formerEmployments (
    companyId bigint not null,
    employmentId bigint not null,
    contactId bigint not null,
    primary key (contactId, employmentId)
);
create table people (
    id bigint not null auto_increment,
    name varchar(255),
    employerId bigint,
    startDate datetime,
    primary key (id)
);
alter table formerEmployments
    add index FK423588D65DE790EF (employmentId),
    add constraint FK423588D65DE790EF
    foreign key (employmentId)
    references employments (id);
alter table formerEmployments
    add index FK423588D67C86A98C (contactId),
    add constraint FK423588D67C86A98C
    foreign key (contactId)
    references people (id);
alter table formerEmployments
    add index FK423588D618D99559 (companyId),
    add constraint FK423588D618D99559
    foreign key (companyId)
    references companies (id);
alter table people
    add index FKC4E2328F7713A817 (employerId),
    add constraint FKC4E2328F7713A817
    foreign key (employerId)
    references companies (id);


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

Code example can be found at http://www.vtype.com/bbchops/test.zip which also includes the complete debug output of the test program execution

Full stack trace of any exception that occurs:
Code:
     [java] 6547 [main] WARN org.hibernate.util.JDBCExceptionReporter  - SQL Error: 1216, SQLState: 23000
     [java] 6547 [main] ERROR org.hibernate.util.JDBCExceptionReporter  - Cannot add or update a child row: a foreign key constraint fails
     [java] 6547 [main] ERROR org.hibernate.event.def.AbstractFlushingEventListener  - Could not synchronize database state with session
     [java] org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
     [java] org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
     [java]    at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:74)
     [java]    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
     [java]    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:181)
     [java]    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:226)
     [java]    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
     [java]    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
     [java]    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
     [java]    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
     [java]    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:324)
     [java]    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)
     [java]    at test.Tester.editData(Tester.java:50)
     [java]    at test.Tester.test(Tester.java:31)
     [java]    at test.Tester.main(Tester.java:24)
     [java]    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     [java]    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     [java]    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     [java]    at java.lang.reflect.Method.invoke(Method.java:324)
     [java]    at org.apache.tools.ant.taskdefs.ExecuteJava.run(ExecuteJava.java:193)
     [java]    at org.apache.tools.ant.taskdefs.ExecuteJava.execute(ExecuteJava.java:130)
     [java]    at org.apache.tools.ant.taskdefs.Java.run(Java.java:705)
     [java]    at org.apache.tools.ant.taskdefs.Java.executeJava(Java.java:177)
     [java]    at org.apache.tools.ant.taskdefs.Java.execute(Java.java:83)
     [java]    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
     [java]    at org.apache.tools.ant.Task.perform(Task.java:364)
     [java]    at org.apache.tools.ant.Target.execute(Target.java:341)
     [java]    at org.apache.tools.ant.Target.performTasks(Target.java:369)
     [java]    at org.apache.tools.ant.Project.executeTarget(Project.java:1214)
     [java]    at org.apache.tools.ant.Project.executeTargets(Project.java:1062)
     [java]    at org.apache.tools.ant.Main.runBuild(Main.java:673)
     [java]    at org.apache.tools.ant.Main.startAnt(Main.java:188)
     [java]    at org.apache.tools.ant.launch.Launcher.run(Launcher.java:196)
     [java]    at org.apache.tools.ant.launch.Launcher.main(Launcher.java:55)
     [java] Caused by: java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails
     [java]    at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:822)
     [java]    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
     [java]    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:174)
     [java]    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:226)
     [java]    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
     [java]    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
     [java]    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
     [java]    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
     [java]    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:324)
     [java]    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)
     [java]    at test.Tester.editData(Tester.java:50)
     [java]    at test.Tester.test(Tester.java:31)
     [java]    at test.Tester.main(Tester.java:24)
     [java]    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     [java]    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     [java]    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     [java]    at java.lang.reflect.Method.invoke(Method.java:324)
     [java]    at org.apache.tools.ant.taskdefs.ExecuteJava.run(ExecuteJava.java:193)
     [java]    at org.apache.tools.ant.taskdefs.ExecuteJava.execute(ExecuteJava.java:130)
     [java]    at org.apache.tools.ant.taskdefs.Java.run(Java.java:705)
     [java]    at org.apache.tools.ant.taskdefs.Java.executeJava(Java.java:177)
     [java]    at org.apache.tools.ant.taskdefs.Java.execute(Java.java:83)
     [java]    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
     [java]    at org.apache.tools.ant.Task.perform(Task.java:364)
     [java]    at org.apache.tools.ant.Target.execute(Target.java:341)
     [java]    at org.apache.tools.ant.Target.performTasks(Target.java:369)
     [java]    at org.apache.tools.ant.Project.executeTarget(Project.java:1214)
     [java]    at org.apache.tools.ant.Project.executeTargets(Project.java:1062)
     [java]    at org.apache.tools.ant.Main.runBuild(Main.java:673)
     [java]    at org.apache.tools.ant.Main.startAnt(Main.java:188)
     [java]    at org.apache.tools.ant.launch.Launcher.run(Launcher.java:196)
     [java]    at org.apache.tools.ant.launch.Launcher.main(Launcher.java:55)

Name and version of the database you are using:

MySQL 4.1.12-standard

The generated SQL (show_sql=true):

Code:
     [java] Hibernate: insert into formerEmployments (contactId, employmentId) values (?, ?)


Debug level Hibernate log excerpt:

Code:
     [java] 6406 [main] DEBUG org.hibernate.impl.SessionImpl  - automatically flushing session
     [java] 6406 [main] DEBUG org.hibernate.event.def.AbstractFlushingEventListener  - flushing session
     [java] 6406 [main] DEBUG org.hibernate.event.def.AbstractFlushingEventListener  - processing flush-time cascades
     [java] 6406 [main] DEBUG org.hibernate.engine.Cascades  - processing cascade ACTION_SAVE_UPDATE for: test.Person
     [java] 6406 [main] DEBUG org.hibernate.engine.Cascades  - cascade ACTION_SAVE_UPDATE for collection: test.Person.formerEmployers
     [java] 6406 [main] DEBUG org.hibernate.engine.Cascades  - cascading to saveOrUpdate: test.Employment
     [java] 6406 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener  - transient instance of: test.Employment
     [java] 6406 [main] DEBUG org.hibernate.event.def.DefaultSaveOrUpdateEventListener  - saving transient instance
     [java] 6406 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener  - saving [test.Employment#<null>]
     [java] 6406 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener  - executing insertions
     [java] 6406 [main] DEBUG org.hibernate.persister.entity.BasicEntityPersister  - Inserting entity: test.Employment (native id)
     [java] 6406 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
     [java] 6406 [main] DEBUG org.hibernate.SQL  - insert into employments (startDate, endDate) values (?, ?)
     [java] Hibernate: insert into employments (startDate, endDate) values (?, ?)
     [java] 6406 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - preparing statement
     [java] 6406 [main] DEBUG org.hibernate.persister.entity.BasicEntityPersister  - Dehydrating entity: [test.Employment#<null>]
     [java] 6406 [main] DEBUG org.hibernate.type.TimestampType  - binding '2005-09-27 13:26:58' to parameter: 1
     [java] 6406 [main] DEBUG org.hibernate.type.TimestampType  - binding '2005-09-27 13:26:59' to parameter: 2
     [java] 6406 [main] DEBUG org.hibernate.id.IdentifierGeneratorFactory  - Natively generated identity: 1
     [java] 6406 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
     [java] 6422 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - closing statement
     [java] 6422 [main] DEBUG org.hibernate.engine.Cascades  - done cascade ACTION_SAVE_UPDATE for collection: test.Person.formerEmployers
     [java] 6422 [main] DEBUG org.hibernate.engine.Cascades  - done processing cascade ACTION_SAVE_UPDATE for: test.Person
     [java] 6422 [main] DEBUG org.hibernate.event.def.AbstractFlushingEventListener  - dirty checking collections
     [java] 6422 [main] DEBUG org.hibernate.event.def.AbstractFlushingEventListener  - Flushing entities and processing referenced collections
     [java] 6438 [main] DEBUG org.hibernate.event.def.WrapVisitor  - Wrapped collection in role: test.Person.formerEmployers
     [java] 6438 [main] DEBUG org.hibernate.persister.entity.BasicEntityPersister  - test.Person.employer is dirty
     [java] 6438 [main] DEBUG org.hibernate.persister.entity.BasicEntityPersister  - test.Person.startDate is dirty
     [java] 6438 [main] DEBUG org.hibernate.event.def.DefaultFlushEntityEventListener  - Updating entity: [test.Person#1]
     [java] 6438 [main] DEBUG org.hibernate.engine.Collections  - Collection found: [test.Person.formerEmployers#1], was: [<unreferenced>] (initialized)
     [java] 6438 [main] DEBUG org.hibernate.event.def.AbstractFlushingEventListener  - Processing unreferenced collections
     [java] 6438 [main] DEBUG org.hibernate.event.def.AbstractFlushingEventListener  - Scheduling collection removes/(re)creates/updates
     [java] 6453 [main] DEBUG org.hibernate.event.def.AbstractFlushingEventListener  - Flushed: 0 insertions, 1 updates, 0 deletions to 4 objects
     [java] 6453 [main] DEBUG org.hibernate.event.def.AbstractFlushingEventListener  - Flushed: 1 (re)creations, 0 updates, 0 removals to 1 collections
     [java] 6453 [main] DEBUG org.hibernate.pretty.Printer  - listing entities:
     [java] 6453 [main] DEBUG org.hibernate.pretty.Printer  - test.Company{formerEmployees=null, employees=null, name=My Company, id=1}
     [java] 6453 [main] DEBUG org.hibernate.pretty.Printer  - test.Company{formerEmployees=null, employees=null, name=Rival Company, id=2}
     [java] 6453 [main] DEBUG org.hibernate.pretty.Printer  - test.Employment{startDate=2005-09-27 13:26:58, endDate=2005-09-27 13:26:59, id=1}
     [java] 6453 [main] DEBUG org.hibernate.pretty.Printer  - test.Person{startDate=2005-09-27 13:26:59, employer=test.Company#1, formerEmployers=[test.Employment#1], name=Paul Example, id=1}
     [java] 6453 [main] DEBUG org.hibernate.event.def.AbstractFlushingEventListener  - executing flush
     [java] 6453 [main] DEBUG org.hibernate.persister.entity.BasicEntityPersister  - Updating entity: [test.Person#1]
     [java] 6453 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
     [java] 6453 [main] DEBUG org.hibernate.SQL  - update people set name=?, employerId=?, startDate=? where id=?
     [java] Hibernate: update people set name=?, employerId=?, startDate=? where id=?
     [java] 6453 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - preparing statement
     [java] 6469 [main] DEBUG org.hibernate.persister.entity.BasicEntityPersister  - Dehydrating entity: [test.Person#1]
     [java] 6469 [main] DEBUG org.hibernate.type.StringType  - binding 'Paul Example' to parameter: 1
     [java] 6469 [main] DEBUG org.hibernate.type.LongType  - binding '1' to parameter: 2
     [java] 6469 [main] DEBUG org.hibernate.type.TimestampType  - binding '2005-09-27 13:26:59' to parameter: 3
     [java] 6469 [main] DEBUG org.hibernate.type.LongType  - binding '1' to parameter: 4
     [java] 6469 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - Adding to batch
     [java] 6469 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - Executing batch size: 1
     [java] 6469 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
     [java] 6469 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - closing statement
     [java] 6469 [main] DEBUG org.hibernate.persister.collection.AbstractCollectionPersister  - Inserting collection: [test.Person.formerEmployers#1]
     [java] 6469 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
     [java] 6469 [main] DEBUG org.hibernate.SQL  - insert into formerEmployments (contactId, employmentId) values (?, ?)
     [java] Hibernate: insert into formerEmployments (contactId, employmentId) values (?, ?)
     [java] 6469 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - preparing statement
     [java] 6469 [main] DEBUG org.hibernate.type.LongType  - binding '1' to parameter: 1
     [java] 6469 [main] DEBUG org.hibernate.type.LongType  - binding '1' to parameter: 2
     [java] 6469 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - Adding to batch
     [java] 6484 [main] DEBUG org.hibernate.persister.collection.AbstractCollectionPersister  - done inserting collection: 1 rows inserted
     [java] 6484 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - Executing batch size: 1
     [java] 6516 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
     [java] 6516 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - closing statement
     [java] 6516 [main] DEBUG org.hibernate.util.JDBCExceptionReporter  - Could not execute JDBC batch update [insert into formerEmployments (contactId, employmentId) values (?, ?)]
     [java] java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails



This is basically a cleaned-up version of code I am writing for a crm app. There are 3 entities: Company, Person and Employment. Company and Person have 2 relationships: a one-to-many for current employers/employees and a many-to-many relationship involving the Employment entity for former employments. This additional class includes extra information such as start/end dates, reason for move, etc. What I would like to do is have Sets in both Company and Person for these old employment records but when I add an Employment to the Set in Person (the non-inverse end of the relationship), only the employment and person id are inserted into the link table, causing a foreign key constraint violation.

How can I ensure that the company id is also recorded, or am I approaching this problem from the wrong angle?
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.