0

This is not a duplicate question. My question is a conceptual one rather than a coding one.

So I have 2 tables whose schema is given below:

create table classroom
(
    building    varchar(15),
    room_number varchar(7),
    capacity    numeric(4, 0),
    primary key (building, room_number)
);

create table section
(
    course_id    varchar(8),
    sec_id       varchar(8),
    semester     varchar(6)
        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
    year         numeric(4, 0) check (year > 1701 and year < 2100),
    building     varchar(15),
    room_number  varchar(7),
    time_slot_id varchar(4),
    primary key (course_id, sec_id, semester, year),
    foreign key (course_id) references course (course_id)
        on delete cascade,
    foreign key (building, room_number) references classroom (building, room_number)
        on delete set null
);

you can see that in section table, 2 fields are foreign keys to 2 fields on another table. When modeling these in JPA, previous questions and answers point to create 2 one-many relations with 2 attributes on entity. Like this for example:

@IdClass(SectionId.class)
@Entity
@Table(name = "section")
public class Section {

    @Id
    private String courseId;

    @Id
    private String secId;

    @Id
    private String semester;

    @Id
    private Integer year;

    private String building;

    private String roomNumber;

    private String timeSlotId;

    // I'm only adding necessary parts of the table omitting getters, setters and other relationships

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.REMOVE)
    @JoinColumn(name = "building")
    private Classroom classroomBuilding;

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.REMOVE)
    @JoinColumn(name = "room_number")
    private Classroom classroomNumber;
}

But unlike other examples, I don't need 2 different parameters on my entity to refer to the other table (entity). I only need a reference to "classroom" which is usually possible for a single foreign key. But here, because of 2 foreign keys, I had to create 2 parameters in entity.

So I though of this approach:

@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.REMOVE)
@JoinColumns(value = {
        @JoinColumn(name = "building"),
        @JoinColumn(name = "room_number")
})
private Classroom classroom;

This looks clear and concise. But I wonder will this approach work or not. And what particular benefits would I have if I opt in for the first approach?

ahrooran
  • 931
  • 1
  • 10
  • 25
  • The second approach will not setup the right composite primary key IIUC. – Tim Biegeleisen Jan 03 '22 at 06:52
  • so i'm stuck with 1st option ??? is there a better way? – ahrooran Jan 03 '22 at 07:06
  • [Check this helpful SO question/answers](https://stackoverflow.com/questions/41143913/sql-jpa-multiple-columns-as-primary-key) for some good options. – Tim Biegeleisen Jan 03 '22 at 07:07
  • I already use @IdClass for primary composite key in both entities. This is just for foreign key. I omitted that in this question to keep it simple. Regardless https://www.baeldung.com/jpa-join-column shows that 2nd approach is indeed possible. – ahrooran Jan 03 '22 at 07:11
  • 1
    Yes, the join itself should work fine, but does the second approach actually create a composite primary key in the parent table? This is the big question here. – Tim Biegeleisen Jan 03 '22 at 07:14

0 Answers0