I have two tables:
ObjectContainer
containerID INT NOT NULL PRIMARY KEY
mainObjectID INT NOT NULL FOREIGN KEY REFERENCES Object (objectID)
Object
objectID INT NOT NULL PRIMARY KEY
containerID INT NOT NULL FOREIGN KEY REFERENCES ObjectContainer (containerID)
I have a circular reference because I wanted to store the main object in an instance of ObjectContainer. Now I don't want the circular-reference anymore because I read there could be problems using a MySQL-Database. So I added a third table "MainObject".
ObjectContainer
containerID INT NOT NULL PRIMARY KEY
Object
objectID INT NOT NULL PRIMARY KEY
containerID INT NOT NULL FOREIGN KEY REFERENCES ObjectContainer (containerID)
MainObject
containerID INT NOT NULL PRIMARY KEY
objectID INT NOT NULL
FOREIGN KEY (containerID, objectID) REFERENCES Object (containerID, objectID)
How can I do this in JPA?
This are my current classes:
@Entity
@Table(name = "tb_objectcontainer)
public class ObjectContainer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "c_id")
private Integer id;
@OneToOne
@JoinColumn(name = "c_main_object_id", nullable = true, referencedColumnName = "c_id")
private Object mainObject;
...
}
and
@Entity
@Table(name = "tb_object", uniqueConstraints = {
@UniqueConstraint(columnNames = { "c_object_container_id", "c_name" }) })
public class Object {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "c_id")
private Integer id;
@Column(name = "c_name", nullable = false)
private String name;
@ManyToOne
@JoinColumn(name = "c_object_container_id", nullable = false, referencedColumnName = "c_id")
private ObjectContainer objectContainer;
@ManyToOne
@JoinColumn(name = "c_parent_id")
private Object parent;
@OneToMany
private List<Object> children = new ArrayList<>();
...
}
mainObjectID
fromObjectContainer
but addisPrimary
intoObject
and maybe add unique index by(containerID, NULLIF(isPrimary, FALSE))
.FOREIGN KEYs
cause more grief than benefit.