Sample Header Ad - 728x90

Use 2 columns of a table as foreign key in an other table to avoid circular-reference

1 vote
0 answers
459 views
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 children = new ArrayList();
...
}
Asked by Xudla (11 rep)
Mar 16, 2023, 02:22 PM
Last activity: Mar 17, 2023, 06:56 AM