I have two normal tables and one relation table.
--------------------------------------------------
| Group | Membership | User |
| (ID, NAME) |(GRP_ID, U_ID) |(ID, FORENAME) |
--------------------------------------------------
| 1, Admin | 1, 1 | 1, Joe |
--------------------------------------------------
The membership has two Foreign Keys
FK1 Membership.GRP_ID -> Group.ID
FK2 Membership.U_ID -> User.ID
I can set cascade-delete to each of the foreign keys (FK1 and FK2).
Fact 1
If neither FK1 nor FK2 does delete cascade, I can neither remove Admin nor Joe because they remain connected in "membership".
CREATE TABLE Membership (
GRP_ID INT NOT NULL,
U_ID INT NOT NULL,
FOREIGN KEY (GRP_ID) REFERENCES Group (id),
FOREIGN KEY (U_ID) REFERENCES User (id)
);
Fact 2
If FK1 cascade delete but FK2 not, you can delete Admin (what removes the membership) but you can not delete Joe.
CREATE TABLE Membership (
GRP_ID INT NOT NULL,
U_ID INT NOT NULL,
FOREIGN KEY (GRP_ID) REFERENCES Group (id) ON DELETE CASCADE,
FOREIGN KEY (U_ID) REFERENCES User (id)
);
Fact 3
If FK2 cascade delete but FK1 not, you can delete Joe (what removes the membership) but you can not delete Admin.
CREATE TABLE Membership (
GRP_ID INT NOT NULL,
U_ID INT NOT NULL,
FOREIGN KEY (GRP_ID) REFERENCES Group (id),
FOREIGN KEY (U_ID) REFERENCES User (id) ON DELETE CASCADE
);
Fact 4
If FK2 cascade delete and FK1 cascade delete. Deletion of Joe will remove the Membership but Admin remains unchanged. Deletion of Admin will remove the Membership but Joe remains unchanged.
CREATE TABLE Membership (
GRP_ID INT NOT NULL,
U_ID INT NOT NULL,
FOREIGN KEY (GRP_ID) REFERENCES Group (id) ON DELETE CASCADE,
FOREIGN KEY (U_ID) REFERENCES User (id) ON DELETE CASCADE
);
Java Code of Group:
// @formatter:off
import javax.persistence.*;
import javax.persistence.Entity;
import javax.persistence.Table;
import java.io.Serializable;
import javax.persistence.ManyToOne;
import javax.persistence.JoinColumn;
import javax.persistence.Column;
import javax.persistence.Version;
import javax.persistence.SequenceGenerator;
import javax.persistence.Id;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import org.hibernate.envers.Audited;
import java.util.LinkedHashSet;
import javax.persistence.OneToMany;
import java.util.Set;
import javax.persistence.OrderBy;
import javax.persistence.ManyToMany;
import javax.persistence.JoinTable;
@Entity
@Table(name="Group")
@SuppressWarnings("serial")
public class Group implements java.io.Serializable {
private Integer id;
private Set<User> users = new LinkedHashSet<>();
private String name;
@Id
@Column(name = "ID", nullable = false)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "gen241738")
@SequenceGenerator(name = "gen241738", sequenceName = "seq_group_id")
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@OrderBy
@ManyToMany
@JoinTable(name="Membership", joinColumns = {
@JoinColumn(name="GRP_ID", referencedColumnName="ID", nullable=false),
}, inverseJoinColumns = {
@JoinColumn(name="U_ID", referencedColumnName="ID", nullable=false)
})
public Set<User> getUsers() {
return users;
}
public void setUsers(Set<User> users) {
this.users = users;
}
@Column(name = "NAME", nullable = false)
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
}
//@formatter:on
Question
When a relation table contains two foreign keys scaling the possibilities to 2x2=4 cases, why does the annotation @ManyToMany allows only one delete-cascadation?
