I have such database tables:
And these are the foreign key constraints:
-- If current variable is being referenced by another variable, restrict deletion
ALTER TABLE variable_variable
ADD CONSTRAINT variable_variable_variable_id_to_fkey
FOREIGN KEY (variable_id_to) REFERENCES variable (id)
ON DELETE RESTRICT;
-- If current variable reference other variable, delete its link by cascade
ALTER TABLE variable_variable
ADD CONSTRAINT variable_variable_variable_id_from_fkey
FOREIGN KEY (variable_id_from) REFERENCES variable (id)
ON DELETE CASCADE;
So, if I have references like:
Other Variable -> Current Variable
, then Current Variable
deletion has to be prohibitedCurrent Variable -> Other Variable
, then Current Variable
deletion should also remove link from Current Variable
to Other Variable
on cascade (links are stored in variable_variable
table).Here is my Hibernate entity for variable
table.
@Entity
@Table(name = "variable")
@Getter
@Setter(PROTECTED)
@NoArgsConstructor(access = PROTECTED)
@DynamicUpdate
public class Variable {
@EmbeddedId
private VariableId id;
@ManyToMany(fetch = LAZY)
@JoinTable(
name = "variable_variable",
joinColumns = @JoinColumn(name = "variable_id_from"),
inverseJoinColumns = @JoinColumn(name = "variable_id_to")
)
private Set<Variable> variables = new HashSet<>();
@ManyToMany(fetch = LAZY)
@JoinTable(
name = "variable_variable",
joinColumns = @JoinColumn(name = "variable_id_to", updatable = false, insertable = false),
inverseJoinColumns = @JoinColumn(name = "variable_id_from", updatable = false, insertable = false)
)
// this collection is readonly and never updates
private Set<Variable> inverseVariables = new HashSet<>();
...
}
So, ManyToMany
relationships are on the owning side and I have no CascadeType.REMOVE
.
But if I call EntityManager.remove(variable)
, those SQL statements are being invoked:
Query:["delete from variable_variable where variable_id_to=?"]
Params:[(7fbbf360-74a0-48db-a1e2-d3b1ab0a869f)]
Query:["delete from variable_variable where variable_id_from=?"]
Params:[(7fbbf360-74a0-48db-a1e2-d3b1ab0a869f)]
Query:["delete from variable where id=?"]
Params:[(7fbbf360-74a0-48db-a1e2-d3b1ab0a869f)]
Hibernate deletes links from variable_variable
prematurely. However, that's not the behavior I need. It breaks the whole idea of complex database constraints in variable_variable
table I described earlier. So, I don't want Hibernate to delete anything from there on its own basis.
I managed to solve this problem with native query:
em.createNativeQuery("DELETE FROM variable WHERE id = :id")
.setParameter("id", variableId)
.executeUpdate();
Anyway, I'd like not to introduce native queries. Is it possible to tell Hibernate not to delete variable_variable
rows on Variable
removing?
I managed to solve the problem with mappedBy
inverse reference. Look at the code snippet below:
@Entity
@Table(name = "variable")
@Getter
@Setter(PROTECTED)
@NoArgsConstructor(access = PROTECTED)
@DynamicUpdate
public class Variable {
@EmbeddedId
private VariableId id;
@ManyToMany(fetch = LAZY)
@JoinTable(
name = "variable_variable",
joinColumns = @JoinColumn(name = "variable_id_from"),
inverseJoinColumns = @JoinColumn(name = "variable_id_to")
)
private Set<Variable> variables = new HashSet<>();
@ManyToMany(fetch = LAZY, mappedBy = "variables")
// this collection is readonly and never updates
private Set<Variable> inverseVariables = new HashSet<>();
...
}
Now when I call em.remove(variable)
, those SQL statements are generated:
Query:["delete from variable_variable where variable_id_from=?"]
Params:[(7fbbf360-74a0-48db-a1e2-d3b1ab0a869f)]
Query:["delete from variable where id=?"]
Params:[(7fbbf360-74a0-48db-a1e2-d3b1ab0a869f)]
Only Current Variable
and references Current Variable -> Other Variable
are being deleted. If there references Other Variable -> Current Variable
, then deletion fails due to database integrity violation. Just what I need