Hibernate version:
3.2.2 GA

Mapping documents:


Full stack trace of any exception that occurs:
Cannot delete or update a parent row: a foreign key constraint fails (`hib_test/person`, CONSTRAINT `FK8C768F554CCEF60C` FOREIGN KEY (`attributesId`) REFERENCES `attributes` (`id`))

Name and version of the database you are using:
MySQL5.0

The generated SQL (show_sql=true):
select attributes0_.id as id2_0_, attributes0_.hairColor as hairColor2_0_, attributes0_.eyeColor as eyeColor2_0_ from ATTRIBUTES attributes0_ where attributes0_.id=?
delete from ATTRIBUTES where id=?

The Scenario

I have a one-to-many relationship between Attributes and Person.

Attributes {
private Set<Person> people;
}

Person {
private Attributes attributes;
}

In my test case, I create a Person p and a linked Attributes a.

In a new transaction, I delete the attributes:

Attributes attributes = getHibernateTemplate().get(Attributes.class,id);
getHibernateTemplate().delete(attributes);

This generates the two SQL statements above, but fails to null out the foreign key (attributesId) for all Person records that may point to that Attributes that is being deleted.

Is there a way that Hibernate can automate this? I don't want to explicitly have to null out that foreign key every time I delete an Attributes, if possible.

I would like to see something like:

select attributes0_.id as id2_0_, attributes0_.hairColor as hairColor2_0_, attributes0_.eyeColor as eyeColor2_0_ from ATTRIBUTES attributes0_ where attributes0_.id=?
update PERSON set attributeId = NULL where attributeId = ?
delete from ATTRIBUTES where id=?

Many thanks for any help.

In java you need to set the Attribute to null in all its associated Person objects - then hibernate will generate the sql to null out the fk. Person is the controlling end of the relationship.

Hi Mike,

Thanks for the reply. Doing something like this:



does work, but I think we were hoping that there would be some way to have Hibernate manage this.. In part because the above code generates two SQL calls for every person that was related to that attributes entity being deleted:

1. Load person where id = ?
2. Update person and null out attributesId

This could generate quite a few queries.. We are looking for a way for Hibernate to do this automatically for us and simply generate one query:

Update person set attributesId = NULL where attributesId = ?

We've migrated to Hibernate3 from EJB 2.1, and we enjoyed this type of behavior in EJB, and were hoping there was some equivalent in Hibernate Land.

But, hibernate dosnt support it -

http://opensource.atlassian.com/projects/hibernate/browse/ANN-598

There is few open JIRA for this.

You can write an interceptor that does it for you.

Thanks to all for the help. I will go the way of the Interceptors for now.

Hi,

I work with cvandyck and am investigating using a Hibernate Interceptor to deal with this problem. From the Interceptor documentation:



I'm not completely certain of the meaning of this, but it sounds problematic given that I was going to write a custom method on my PersonDAO to null the attibutesId of all Persons referencing the Attribute that I am deleting. My PersonDAO implementation *definitely* relies on the session.

Is there any other way of easily querying the database from something that implements this interceptor to null the foreign keys of the Person records?

Thanks,
Bradley