[FIXED] How to link foreign key between @Entity in spring boot data jpa

Issue

Technologies:

1. mysql 8
2. spring-boot-starter-parent 2.3.3.RELEASE
3. With spring-boot-starter-data-jpa

Error:

nested exception is org.springframework.dao.DataIntegrityViolationException: **could not execute statement; SQL [n/a]; constraint [null];** 
java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`emr`.`user_detail`, CONSTRAINT `user_detail_ibfk_1` FOREIGN KEY (`id`) REFERENCES `facility` (`id`))

DDLs:

create table facility
(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name varchar(60),
    address varchar(200)
);
    
create table user_detail
(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    username varchar(20) not null unique,
    contactNo varchar(12),
    facilityId int unsigned
    foreign key (id) references facility(id)
);

Java classes:

@Entity
@Table(name= "user_detail")
public class User {
    
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;
    private String username;

    @Column (name="contactNo")
    private String contactNo;
        
    @Column (name="facilityId")
    private Integer facilityId;
    
    //getter and setter
}
    
@Entity
@Table(name="facility")
public class Facility {
    
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
    private String name;
}

Let's say facility table has 1 row with Id=1 and name='Facility 1' I'm trying to insert a row in user_detail table for facilityId=1 but getting error message

As much I understand it is unable to find that facilityId is field id in Facility.java and assuming null value which is not permissible in user_detail.facilityId column

I'm completely stumped at making code understand that id is the Field of foreign key. Tried some combinations of @JoinColumn without success.

Solution

The exception shows that when you try to save User it saves null as facilityId.

To make this work you need to specify the Facility as another table in User entity (you did it wrong way).

@Entity
@Table(name= "user_detail")
public class User {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;
    private String username;
    @Column (name="contactNo")
    private String contactNo;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "facilityId", referencedColumnName = "id")
    private Facility facility;
    
    //getter and setter
}

Based on your requirement you need to specify the relationship. It seems here that the relationship should be one to one so I marked it as such.

Answered By - Sam