ConstraintViolationException: could not delete: [com.pizza.domain.Base#4]

[vinnyboy]

I followed Spring Roo 1.1.0.M2 tutorial down to page 16 to produce pizzashop and perform test after adding field reference Base to Pizza. The test run ok 10 out of 10 times. However, if I change the Pizza domain to have Base all the time (ie adding @NotNull reference to Base) and perform test again. It fails about 3 or 4 out of 10 test cases. Each time the Base # is different.

[Thread-13] 2010-07-31 22:52:46,593 [main] ERROR org.hibernate.util.JDBCExceptionReporter - integrity constraint violation: foreign key no a
ction; FK65BDC883CAA6D19 table: PIZZA
[Thread-13] 2010-07-31 22:52:46,593 [main] ERROR org.hibernate.event.def.AbstractFlushingEventListe ner - Could not synchronize database stat
e with session
[Thread-13] org.hibernate.exception.ConstraintViolationExcepti on: could not delete: [com.pizza.domain.Base#4]
[Thread-13] at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:96)

---------------------------------
Thread-23] 2010-07-31 23:21:50,312 [main] ERROR org.hibernate.event.def.AbstractFlushingEventListe ner - Could not synchronize database stat
with session
Thread-23] org.hibernate.exception.ConstraintViolationExcepti on: could not delete: [com.pizza.domain.Base#10]
Thread-23] at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:96)
--------------------------
[Thread-29] 2010-07-31 23:24:14,171 [main] ERROR org.hibernate.event.def.AbstractFlushingEventListe ner - Could not synchronize database stat
e with session
[Thread-29] org.hibernate.exception.ConstraintViolationExcepti on: could not delete: [com.pizza.domain.Base#5]
[Thread-29] at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:96)
-------------------------
[Thread-29] Caused by: org.hsqldb.HsqlException: integrity constraint violation: foreign key no action; FK65BDC883CAA6D19 table: PIZZA
[Thread-29] at org.hsqldb.error.Error.error(Unknown Source)
[Thread-29] at org.hsqldb.StatementDML.performReferentialActions( Unknown Source)
[Thread-29] at org.hsqldb.StatementDML.delete(Unknown Source)
[Thread-29] at org.hsqldb.StatementDML.executeDeleteStatement(Unk nown Source)
[Thread-29] at org.hsqldb.StatementDML.getResult(Unknown Source)
[Thread-29] at org.hsqldb.StatementDMQL.execute(Unknown Source)
[Thread-29] at org.hsqldb.Session.executeCompiledStatement(Unknow n Source)
[Thread-29] at org.hsqldb.Session.execute(Unknown Source)
[Thread-29] ... 57 more
[Thread-29] Tests run: 9, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.062 sec <<< FAILURE!
[Thread-29] za roo>
[Thread-29] Results :
[Thread-29] za roo>
[Thread-29] Tests in error:
[Thread-29] testRemove(com.pizza.domain.BaseIntegrationTest)
[Thread-29] za roo>
[Thread-29] Tests run: 27, Failures: 0, Errors: 1, Skipped: 0

[vinnyboy]

If I have more of this kind of reference on other domain, it is harder to pass the test. I have about a dozen of entity have these kind of references out of 3 dozen. I couldn't get the test to pass successfully because of "ConstraintViolationException: could not delete:"

[wrochow]

In my opinion, this is working as it should. When the tests run, they create a Pizza that references your Base. The clue is at the bottom of com.pizza.domain.BaseIntegrationTest.txt in target/surfire-reports.

Caused by: java.sql.SQLException: Integrity constraint violation FK65BDC883CAA6D19 table: PIZZA in statement [delete from base where id=? and version=?]
	at org.hsqldb.jdbc.Util.throwError(Unknown Source)
	at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2694)
	... 52 more

If this was a real application, you would not allow the end user to delete a base that is in use, but would instruct them to change the Bases in all existing Pizzas, and then allow them to delete the offending Base. Of course, you would write unit tests to cover that scenario.

Of course, if you just want to see that the tests all pass, modify the ManyToOne annotation like so:

    @ManyToOne(targetEntity = Base.class, cascade=CascadeType.ALL)

That way the unit tests will clean up the Bases as they delete the Pizzas. However, in a real world application, this may not be what you want.

Hope that helps.