1

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<>();
...
}
4
  • Now I don't want the circle-reference anymore Remove mainObjectID from ObjectContainer but add isPrimary into Object and maybe add unique index by (containerID, NULLIF(isPrimary, FALSE)).
    – Akina
    Mar 16 at 17:47
  • @Akina "Remove mainObjectID from ObjectContainer but add isPrimary into Object" this is also an option, but I will end having too much not needed data. This table is going to contain thousands of records.
    – aldux01
    Mar 17 at 6:55
  • but I will end having too much not needed data. 1 byte per row is "too mush"? seriously?
    – Akina
    Mar 17 at 14:26
  • Sometimes FOREIGN KEYs cause more grief than benefit.
    – Rick James
    10 hours ago

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy