One To Many Unidirectional association insert query getting failed

Question

I am having User and Address entity with one-to-many Unidirectional relationship. When I am trying to insert User with Address details, it is getting failed with exception "Referential integrity constraint violation" , as I checked Address is not having inserted userId it has 0 I am not getting what is wrong with this.

My User entity:

        @Entity 
        @Table(name = "users")
        public class User{
        
            @Id
            @GeneratedValue(strategy = GenerationType.IDENTITY)
            @Column(name = "ID")
            private long id;
            
            @OneToMany(orphanRemoval = true,cascade = CascadeType.ALL, fetch=FetchType.EAGER)
            @JoinColumn(name = "USER_ID", referencedColumnName = "ID")
            private List<Address> address;
    // other table columns
        
         }

My Address entity:

    @Entity
    @Table(name = "address")
    public class Address{
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "ID")
        private long id;
        
        @Column(name = "USER_ID")
        private long userId;
    //other table columns
    }

my controller:

    @PostMapping("/")
    public ResponseEntity<UserDTO> saveUser(@RequestBody UserRequestDTO userRequestDTO){
            try {
                if (userRequestDTO != null) {
                    return new ResponseEntity<>(userService.saveUser(userRequestDTO), HttpStatus.CREATED);
                }
            } catch (Exception ex) {
                return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
            }
            return null;
        }

My Service layer:

    @Override
        public UserDTO saveUser(UserRequestDTO userDto) {
            User obj = modelMapper.map(userDto, User.class);
            System.out.println(obj.toString());
            obj = userRepository.save(obj);
            return modelMapper.map(obj, UserDTO.class);
        }

Here is my UserRequestDTO in this I have all property

    public class UserRequestDTO {
    
        private long id;
        private long clientId;
        private String clientName;
        private String email;
        private String firstName;
        private String lastName;
        private String employeeNo;
        private String designation; 
        private String status;
        private String phoneNumber;
        private String employeeType;
        private String reportingTo;
        private String department;
        private String division;
        private String password;
        private String gender;  
        private String bloodGroup;
        private String maritalStatus;
        private String spouseName;
        private String noOfChildren;
        private Date dateOfBirth;
        private Date hiredDate;
        private LocalDate createdDate;
        private String createdBy;
        private LocalDate updatedDate;
        private String updatedBy;
        private List<Address> address;
    }

Checked many online tutorials but not able to get the root cause Error message from console:

    User(id=0, clientId=1, email=user@gmail.com, firstName=user 10, lastName=K, password=password@123, employeeNo=EH5213, phoneNumber=9999999990, status=A, designation=Developer, employeeType=FullTime, reportingTo=Roshan, department=Information Technology, division=division, locationId=null, gender=null, bloodGroup=null, maritalStatus=null, spouseName=null, noOfChildren=null, dateOfBirth=null, hiredDate=null, createdDate=2022-03-25, createdBy=admin, updatedDate=2022-03-25, updatedBy=admin, organization=null, address=[Address(id=0, clientId=1, userId=0, address1=add1Sample, address2=add2Sample, state=sample, addressType=P, country=sample, city=sample, zipCode=000002, primaryPhone=1111111111, secondaryPhone=2222222222, active=true, createdDate=2022-03-25, createdBy=admin, updatedDate=2022-03-25, updatedBy=admin), Address(id=0, clientId=1, userId=0, address1=sample2, address2=add2sample2 , state=sample2, addressType=T, country=sample2, city=sample2, zipCode=000008, primaryPhone=4444444444, secondaryPhone=666666666, active=true, createdDate=2022-03-25, createdBy=admin, updatedDate=2022-03-25, updatedBy=admin)], employeeGroups=null)
    Hibernate: insert into users (id, blood_group, client_id, created_by, created_date, date_of_birth, department, designation, division, email, employee_no, employee_type, first_name, gender, hired_date, last_name, location_id, marital_status, no_of_children, organization_id, password, phone_number, reporting_to, spouse_name, status, updated_by, updated_date) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    Hibernate: insert into address (id, active, address_line_1, address_line_2, address_type, city, client_id, country, created_by, created_date, primary_phone, secondary_phone, state, updated_by, updated_date, user_id, zip_code) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    [2m2022-03-30 12:41:25.689[0;39m [33m WARN[0;39m [35m13308[0;39m [2m---[0;39m [2m[nio-8085-exec-2][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper  [0;39m [2m:[0;39m SQL Error: 23506, SQLState: 23506
    [2m2022-03-30 12:41:25.702[0;39m [31mERROR[0;39m [35m13308[0;39m [2m---[0;39m [2m[nio-8085-exec-2][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper  [0;39m [2m:[0;39m Referential integrity constraint violation: "FK6I66IJB8TWGCQTETL8EEEED6V: PUBLIC.ADDRESS FOREIGN KEY(USER_ID) REFERENCES PUBLIC.USERS(ID) (0)"; SQL statement:
    insert into address (id, active, address_line_1, address_line_2, address_type, city, client_id, country, created_by, created_date, primary_phone, secondary_phone, state, updated_by, updated_date, user_id, zip_code) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23506-200]

My 5 cents - the address you are inserting with user doesn't exist. Check if the address, or the address list, contains valid, e.g. already existing in database, entity. — Evgeni Enchev, Mar 30 '22 at 06:37
@EvgeniEnchev I did debug and when insert into address getting failed then at that time User record is not available — ngi, Mar 30 '22 at 07:17
Up to your log you are inserting user with id = 0 in the address. So insert user, obtain the new id, update address with this id and insert address. It's exactly the reverse of what I said, sorry. — Evgeni Enchev, Mar 30 '22 at 07:33
@ngi, try to do it in two steps - first insert the user, obtain id, update the address' list in user, insert address. I had similar problem a month ago and we solved it this way. — Evgeni Enchev, Mar 30 '22 at 07:54

score 1 · Accepted Answer · answered Mar 30 '22 at 08:31

This can be fixed by some small changes at Entity level:
Change-1: Add nullable=false at your User entity, something like this:

@Entity 
@Table(name = "users")`
public class User{`
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private long id;
        
    @OneToMany(orphanRemoval = true,cascade = CascadeType.ALL, fetch=FetchType.EAGER)
    @JoinColumn(name = "USER_ID", referencedColumnName = "ID", nullable = false)
     private List<Address> address;
// other table columns
}

Change-2: Add insertable = false and updatable = false at your Address entity, something like this

@Entity
@Table(name = "address")
public class Address{
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private long id;

    @Column(name = "USER_ID", insertable = false, updatable = false)
    private long userId;
//other table columns
}

Use of nullable=false : It applies the not null constraint to the particular database column

To to know uses of insertable = false, updatable = false please refer this :Please explain about insertable=false and updatable=false in reference to the JPA @Column annotation

ok let me make the changes and try it – ngi Mar 30 '22 at 08:35 — ngi, Mar 30 '22 at 08:35
After making the changes It is working – ngi Mar 30 '22 at 08:39 — ngi, Mar 30 '22 at 08:39

Faheem azaz Bhanej · Answer 2 · 2022-03-31T04:23:45.320

You have to insert User first then after insert Address means you have to get reference of User before inserting Address. But in Unidirectional Mapping you don't need to put code for insert Address because it automatically get reference from User. DTO is not required here.

Remove this column from Address.java because it is bad pratice to insert foreign key manually:

@Column(name = "USER_ID", insertable = false, updatable = false)
private long userId;

Here down is modified code:

Controller

@RequestMapping(value = "/")
@ResponseBody
public ResponseEntity<User> addUser(@RequestBody User user)
{
    userRepository.save(user);
    return new ResponseEntity<>(user, HttpStatus.CREATED);
}

You don't want to make DTO here...

One To Many Unidirectional association insert query getting failed

2 Answers2