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

user_2216472 0
Viewed 5583

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.

spring-data-jpa hibernate
2 Answers

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.

user_104030220

You should use @MapsId annotation here to borrow the identifier from a many-to-one or one-to-one association.

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

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;
    
    @OneToOne(cascade = CascadeType.ALL)
    @MapsId
    private Facility facility;

    //getter and setter
}
user_62771040
Related