This should be basic, but I can't figure it out. I have a boss and a worker. I can easily delete a department, even if it has workers, but when I try to delete a worker, I get the following error:
failed to execute statement; SQL [n / a]; Restriction ["FK_1LBUSQOSYF0O16356TFILYJPI: PUBLIC.DEPARTAMENT_WORKER FOREIGN KEY (WORKERS_ID) LINKS PUBLIC.WORKER (ID) (1)"; SQL statement: remove from Worker, where id =? [23503-182]]; The nested exception is org.hibernate.exception.ConstraintViolationException: the statement failed
Full stack trace:
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["FK_1LBUSQOSYF0O16356TFILYJPI:
PUBLIC.DEPARTAMENT_WORKER FOREIGN KEY(WORKERS_ID) REFERENCES PUBLIC.WORKER(ID) (1)"; SQL statement:
delete from Worker where id=? [23503-182]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:643)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:104)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:516)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:394)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)
at com.mypack.ui.DepartamentUI$$EnhancerByCGLIB$$25100ace.deleteWorker(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.el.parser.AstValue.invoke(AstValue.java:278)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:274)
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
at javax.faces.event.MethodExpressionActionListener.processAction(MethodExpressionActionListener.java:147)
at javax.faces.event.ActionEvent.processListener(ActionEvent.java:88)
at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:813)
at javax.faces.component.UICommand.broadcast(UICommand.java:300)
at javax.faces.component.UIData.broadcast(UIData.java:1108)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:136)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:58)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3343)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3546)
at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:100)
at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:377)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:369)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:293)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:339)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1234)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:404)
at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175)
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:75)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:512)
... 44 more
Caused by: org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "FK_1LBUSQOSYF0O16356TFILYJPI: PUBLIC.DEPARTAMENT_WORKER
FOREIGN KEY(WORKERS_ID) REFERENCES PUBLIC.WORKER(ID) (1)"; SQL statement:
delete from Worker where id=? [23503-182]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:426)
at org.h2.constraint.ConstraintReferential.checkRowRefTable(ConstraintReferential.java:443)
at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:318)
at org.h2.table.Table.fireConstraints(Table.java:908)
at org.h2.table.Table.fireAfterRow(Table.java:926)
at org.h2.command.dml.Delete.update(Delete.java:100)
at org.h2.command.CommandContainer.update(CommandContainer.java:78)
at org.h2.command.Command.executeUpdate(Command.java:254)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:143)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)
... 59 more
Does the error seem to indicate a poor database design? How can I do it right?
Excerpt from main.xhtml (this works):
<h:commandLink action="#{departamentUI.deleteCurrentDepartament1(departament.id)}" value="Delete"/>
Excerpt from worker.xhtml (this does not work):
<h:commandLink actionListener="#{departamentUI.deleteWorker(worker.id)}" value="Delete" />
Departament.java:
@Entity
public class Departament implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
@OneToMany(fetch = FetchType.EAGER)
private Set<Worker> workers;
-- Getters & Setters & ID generation--
Worker.java:
@Entity
public class Worker implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
private String lastName;
-- Getters & Setters & ID generation--
DepartamentUI.java:
public class DepartamentUI implements Serializable {
@PersistenceContext
private EntityManager entityManager;
private Departament currentDepartament1;
private Worker currentWorker;
private String newWorkerName;
private String newWorkerLastName;
private String newDepartament1Name;
@Transactional
public void deleteWorker(Long id) {
currentWorker = entityManager.find(Worker.class, id);
entityManager.remove(currentWorker);
}
@Transactional
public void deleteCurrentDepartament1(Long id){
currentDepartament1 = entityManager.find(Departament.class, id);
entityManager.remove(currentDepartament1);
}
etc. etc.