How to delete an entity which has a foreign key reference to it?

I think it's a simple and basic question,

I have a one-way @manytomany mapping

user class:
@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(
name="user_role",
joinColumns = { @JoinColumn( name="user_id") },
inverseJoinColumns = @JoinColumn( name="role_id")
)
public Set<Role> getRoles() {
return roles;
}

and database definition for the middle table

CREATE TABLE /*!32312 IF NOT EXISTS*/ `user_role` (
`user_id` bigint(20) NOT NULL,
`role_id` bigint(20) NOT NULL,
PRIMARY KEY (`user_id`,`role_id`),
KEY `FK143BF46AF503D155` (`user_id`),
KEY `FK143BF46A4FD90D75` (`role_id`),
CONSTRAINT `FK143BF46A4FD90D75` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
CONSTRAINT `FK143BF46AF503D155` FOREIGN KEY (`user_id`) REFERENCES `app_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now, my question is I can't delete a role directly

like

this.getHibernateTemplate().delete(role);

, because it has a FK reference on it, so what's the best way to do it in hibernate?

Thanks.

You define foreign-keys (from a database perspective) to enforce referential integrity. Therefore you can't delete the role from the Role table because that would make your database inconsistent. You would have a role that doesn't exist, but is assigned to a user.

The proper way to delete the role would be to delete the records referring to that role from the user-role table first, then delete the role.

If you are determined to delete the role without deleting the records in the user-role table, then you must delete the foreign-key constraints from the database.

you can try a delete with cascade option (it's dangerous) or delete first what it's defined like foreign key and then you can delete what's is defined like "father".

Thanks. I know these basics. Just want to know the best practice to do it in hibernate.

If in your data model the child is owned by the parent entity, ie the child has no existence without a parent then you need to first delete your child record and then only delete the parent. You can automate it with hibernate if the relation is bi-directionally mapped. What you need to do is put cascade="delete" at the one-to-many end of the relationship (ie. in the parent mapping so that means you have to make the relation bi-directional).

If the child is not owned, but just related to the parent, then you need to first reset the foreign key field in the child to null and then delete the parent record. Again if the hibernate mapping is bidirectional and you have not specified cascade delete then hibernate will do this automatically for you.

Consider the entities Subject, Course and Teacher.

Java is a subject

Beginners Java is a Course related to Java.
Advanced Java is a Course related to Java.

Teacher Sam teaches both.

So Subject to Course and Teacher to Course are one to many relations.

But Course exists even if there are no assigned teacher to it. May be a new course just introduced by the institute for which they are looking for a faculty.

But a Course does not exists without a Subject. If the institute decides that they will no longer teach Java then all the courses related to java also stops to exist.

So in Subject to Course relation Course is owned by Subject
But in Teacher to Course relation Course is not owned by teacher

Hope this clarifies the things.

Thanks. I understand what you mean now.

And solved my problem:

@SuppressWarnings("unchecked")
public void removeRoleById(Long roleId) {
Role role = this.get(roleId);

List<User> users = this.getHibernateTemplate().find("select u from User u join u.roles r where r.id = ?", roleId);
for(User user : users){
user.removeRole(role);
this.getHibernateTemplate().saveOrUpdate(user);
}

this.getHibernateTemplate().delete(role);
}

BTW, do you know what's the difference between save() and saveOrUpdate(), can't we just use saveOrUpdate() all the time?

Thanks.

difference between save() and saveOrUpdate() is obvious from the name itself.

save - saves the entity
saveOrUpdate - saves if the entity is not existing Or updates if it exists.

Yes you can use saveOrUpdate all the time.

Thanks. Got it.