javaspring-boothibernatejpaspring-repositories

one-to-many: JPA generates wrong FK while using ddl-auto=create-drop


It seems that I made a mistake somewhere but I can not see where.

JPA insists on generating a unique constraint on a FK of one-to-many relationship on the child side. This constraint prevents me from inserting more than one record in the child table.

Parent entity:

@Entity
@Table(name = "customer")
@Data
public class CustomerDto {
    @Id
    private Long id;

    ...

    @Column(name = "notes")
    private String notes;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    private List<EmailDto> emails = new ArrayList<>();

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    private List<PhoneDto> phones = new ArrayList<>();
}

Child 1:

@Entity
@Table(name = "email")
@Data
public class EmailDto {
    @Id
    private Long id;

    @Column(name = "email", unique = true, length = 128)
    private String email;

    @ManyToOne(fetch = FetchType.LAZY)
    private CustomerDto customer;
}

Child 2:

@Entity
@Table(name = "phone")
@Data
public class PhoneDto {
    @Id
    private Long id;

    @Column(name = "phone_number", nullable = false, length = 32)
    private String phoneNumber;

    @OneToOne(fetch = FetchType.LAZY)
    private CustomerDto customer;
}

The tables looks as I expect:

enter image description here

But this also generates an extra constraint for the foreign-key on the phone side:

enter image description here

The email table looks ok:

enter image description here

The unique constraint that Hibernate generates on the phone side ONLY allows me to insert ONE record per customer. If I try to insert two phone numbers I get a duplicate key value violates unique constraint "phone_customer_id_key" error.

If I change the JoinColumn definition on the CustomerDto.java this way:

@JoinColumn(name = "customer_id") ---> @JoinColumn(name = "customer_idx")

then it works and I can save 0..* phone numbers per customer properly.

But, this modification generates a stupid table structure:

enter image description here

What I am missing here?

I can create a proper structure with simple SQLs and use customer_id as a foreign key in the child tables. But why JPA generate an extra unexpected constraint?

---------- UPDATE ----------

I also tried the same before I posted this question, but the result was not as I expected. I changed again the JPA annotations as per the sugestions and I got this:

enter image description here

JPA does not set the foreign keys in the child table.


Solution

  • The following settings provides me the expected DB structure:

    @Entity
    @Table(name = "customer")
    @Data
    public class CustomerDto {
        @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "customer-id-sequence")
        @SequenceGenerator(name = "customer-id-sequence", sequenceName = "customer_id_seq", allocationSize = 1)
        @Column(name = "id")
        private Long id;
    
    
        ...
        ...
    
        @Column(name = "notes")
        private String notes;
    
        @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)
        @JoinColumn(name = "customer_id")
        private List<EmailDto> emails = new ArrayList<>();
    
        @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)
        @JoinColumn(name = "customer_id")
        private List<PhoneDto> phones = new ArrayList<>();
    }
    

    Child:

    @Entity
    @Table(name = "email")
    @Data
    public class EmailDto {
        @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "email-id-sequence")
        @SequenceGenerator(name = "email-id-sequence", sequenceName = "email_id_seq", allocationSize = 1)
        @Column(name = "id")
        private Long id;
    
        @Column(name = "email", unique = true, length = 128)
        private String email;
    
        @ManyToOne()
        private CustomerDto customer;
    }