How to work properly with FK relationships with JPA

Question

I have this entity relationship diagram right here: https://i.stack.imgur.com/wnP3l.png

And i'm trying to do this project. I've done all the classes, setup the relationships and everything else.

But once i tried to run and create a new user, i'm getting the error below:

Foreign key (FK3w0xeq6jk6vt2vi1fydpci0y9:T_PAGAMENTO [CD_CORRIDA])) must have same number of columns as the referenced primary key (T_CORRIDA [CD_PASSAGEIRO,CD_MOTORISTA,CD_CORRIDA])

I've tried to setup those relationships but i got nothing different.

@Entity
@Table(name="T_CORRIDA")
@SequenceGenerator(name="sq_corrida", sequenceName="SEQ_T_CORRIDA", allocationSize=1)
public class Corrida {

    @Id
    @GeneratedValue(generator="sq_corrida", strategy = GenerationType.SEQUENCE)
    @Column(name="CD_CORRIDA")
    private int cd_corrida;

    @Column(name="DS_ORIGEM", length = 150, nullable= false)
    private String ds_origem;

    @Column(name="DS_DESTINO", length= 150, nullable= false)
    private String ds_destino;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name="DT_CORRIDA")
    private Calendar dt_corrida;

    @Column(name="VL_CORRIDA", nullable= false)
    private float vl_corrida;

    @Id
    @ManyToOne(cascade=CascadeType.PERSIST)
    @JoinColumn(name="CD_MOTORISTA")
    private Motorista motorista;

    @Id
    @ManyToOne(cascade=CascadeType.PERSIST)
    @JoinColumn(name="CD_PASSAGEIRO")
    private Passageiro passageiro;

    @OneToMany(mappedBy="corrida", cascade= CascadeType.PERSIST)
    private List<Pagamento> listaPagamento;

@Entity
@Table(name="T_MOTORISTA")
@SecondaryTable(name="T_DADOS_MOTORISTA", pkJoinColumns={@PrimaryKeyJoinColumn(name="NR_CARTEIRA")})
public class Motorista {

    @Id
    @Column(name="NR_CARTEIRA", nullable= false)
    private int nr_carteira;

    @Column(name="NM_MOTORISTA", length= 150, nullable= false)
    private String nm_motorista;

    @Temporal(TemporalType.DATE)
    @Column(name="DT_NASC")
    private Calendar dt_nasc;

    @Lob
    @Column(name="FT_CARTEIRA")
    private byte[] ft_carteira;

    @Enumerated(EnumType.STRING)
    @Column(name="DS_GENERO")
    private TipoSexo ds_genero;

    @ManyToMany(cascade=CascadeType.PERSIST)
    @JoinTable(name="T_VEICULO_MOTORISTA", 
        joinColumns=@JoinColumn(name="NR_CARTEIRA"), 
        inverseJoinColumns=@JoinColumn(name="CD_VEICULO"))
    private List<Veiculo> listaVeiculo;

    @OneToMany(mappedBy="motorista")
    private List<Corrida> corridasMotorista;

    @Column(name="NR_BANCO", table="T_DADOS_MOTORISTA")
    private int banco;

    @Column(name="NR_AGENCIA", table="T_DADOS_MOTORISTA")
    private int agencia;

    @Column(name="NR_CONTA", table="T_DADOS_MOTORISTA")
    private int conta;

@Entity
@Table(name="T_PASSAGEIRO")
@SequenceGenerator(name="seq_passageiro", sequenceName="SEQ_T_PASSAGEIRO", allocationSize=1)
public class Passageiro {

    @Id
    @Column(name="cd_passageiro")
    @GeneratedValue(generator="seq_passageiro", strategy=GenerationType.SEQUENCE)
    private int cd_passageiro;

    @Column(name="NM_PASSAGEIRO", nullable= false, length= 100)
    private String nm_passageiro;

    @Temporal(TemporalType.DATE)
    @Column(name="DT_NASCIMENTO", nullable=false)
    private Calendar dt_nascimento;

    @Enumerated(EnumType.STRING)
    @Column(name="DS_GENERO")
    private TipoSexo ds_genero;

    @OneToMany(mappedBy="passageiro")
    private List<Corrida> corridaPassageiro;

@Entity
@Table(name="T_PAGAMENTO")
@SequenceGenerator(name="seq_pgto", sequenceName="SEQ_T_PGTO", allocationSize=1)
public class Pagamento {

    @Id
    @Column(name="CD_PAGAMENTO", nullable= false)
    @GeneratedValue(generator="seq_pgto", strategy=GenerationType.SEQUENCE)
    private int cd_pagamento;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name="DT_PAGAMENTO", nullable= false)
    private Calendar dt_pagamento;

    @Column(name="VL_PAGAMENTO", nullable= false)
    private float vl_pagamento;

    @Enumerated(EnumType.STRING)
    @Column(name="DS_PAGAMENTO", nullable = false)
    private TipoPagamento ds_forma_pagamento;

    @ManyToOne
    @JoinColumn(name="CD_CORRIDA")
    private Corrida corrida;

I just want to know what i'm doing wrong and why i'm getting the foreign key error, how to set up these keys right. Thanks in any advance!

Read this: https://vladmihalcea.com/the-best-way-to-map-a-composite-primary-key-with-jpa-and-hibernate/ — Fausto Carvalho Marques Silva, May 06 '19 at 21:02
Map a class of "@Embeddable" to the composite primary key CD_PASSAGEIRO,CD_MOTORISTA,CD_CORRIDA. Than use the class to map a "@EmbeddedId" in the T_PAGAMENTO and T_CORRIDA. — Fausto Carvalho Marques Silva, May 06 '19 at 21:24
Possible duplicate of [How to map a composite key with Hibernate?](https://stackoverflow.com/questions/3585034/how-to-map-a-composite-key-with-hibernate) — Fausto Carvalho Marques Silva, May 06 '19 at 21:25
Pagamento's ManyToOne relationship to Corrida only has a single join column "CD_CORRIDA". This doesn't match the foreign key in the database, which is using 3 fields, and doesn't line up with the composite ID you've defined in JPA. You need to use the JoinColumns annotation to define all 3 foreign key fields on this relationship — Chris, May 06 '19 at 21:54
Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Make your post self-contained. Insert images/links using edit functions. — philipxy, May 07 '19 at 03:56

score 1 · Answer 1 · answered May 07 '19 at 07:55

By adding the @Id annotation to the fields cd_corrida, motorista and cd_corrida you have specified that the entity Corrida has a composite PK. In this case, Pagamento must specify the join on the corresponding 3 columns in the T_PAGAMENTO table.

However, as you are using an auto-generated sequence for one of the @ID fields in Corrida then there is no need to have a composite PK as a Corrida can be uniquely identified by this field only:

@Id
@GeneratedValue(generator="sq_corrida", strategy = GenerationType.SEQUENCE)
@Column(name="CD_CORRIDA")
private int cd_corrida;

You can then remove the other 2 @Id annotations. There is no need for the ID to match any PK column(s) defined database: it simply has to be unique and by specifying a sequence for cd_corrida then this alone can be used as identifier by the Entity Manager.

How to work properly with FK relationships with JPA

1 Answers1