0

I have the following context:

CREATE TABLE PROBLEMS (
    NAME VARCHAR PRIMARY KEY,
    AVG_COST DOUBLE PRECISION
);

CREATE TABLE LOCATIONS (
    ID VARCHAR NOT NULL,
    PROBLEM_NAME VARCHAR NOT NULL REFERENCES PROBLEMS(NAME),
    PRIMARY KEY (ID, PROBLEM_NAME)
);

CREATE TABLE MOVEMENTS (
    START_LOC_ID VARCHAR,
    END_LOC_ID VARCHAR,
    PROBLEM_NAME VARCHAR,
    FOREIGN KEY (START_LOC_ID, PROBLEM_NAME ) REFERENCES LOCATIONS(ID, PROBLEM_NAME),
    FOREIGN KEY (END_LOC_ID, PROBLEM_NAME) REFERENCES LOCATIONS(ID, PROBLEM_NAME),
    PRIMARY KEY (START_LOC_ID, END_LOC_ID, PROBLEM_NAME)
);

How should the movement entity look such that the entity is mapped to the above "MOVEMENTS" table using JPA.

I tried the following, but the foreign key for the Locations are not created.

@Entity
@Table(name="movements")
public class Movement {
    @Id
    @ManyToOne(fetch = FetchType.LAZY)
    private Problem problem;

    @Id
    @ManyToOne
    @JoinColumn(name = "start_location_id", referencedColumnName = "id")
    private Location startLocation;

    @Id
    @ManyToOne
    @JoinColumn(name = "end_location_id", referencedColumnName = "id")
    private Location endLocation;
    
    
    private int duration;

    //...
}
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Iulian Muntean
  • 3
  • 2
  • Does this answer your question? [JPA how to make composite Foreign Key part of composite Primary Key](https://stackoverflow.com/questions/31385658/jpa-how-to-make-composite-foreign-key-part-of-composite-primary-key) – Max Apr 01 '23 at 15:36
  • I managed to make it work this way, but I have redundant data in my "movements" table. There are two columns for "problem_name" - which is part of the Location PK. The table looks like this: start_loc_id (FK1) (PK) strart_loc_problem_name (FK1) (PK) end_loc_id (FK2) (PK) end_loc_problem_name. (FK2) (PK) The two foreign keys have one column in common, and I wonder if I could get rid of one so that it looks like in the above "create table" statement. – Iulian Muntean Apr 02 '23 at 08:16
  • 1
    Rather than use multi-column primary keys, I always define a surrogate id because it simplifies everything. – Bohemian Apr 02 '23 at 09:07

0 Answers0