ERROR: update or delete on table "users" violates foreign key constraint "fkoixwfhgxuda232r3m5llu7few" on table "addresses"

Question

I have two entities Users and Addresses with OneToMany mapping from Users.

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue
    private Long id;

    @Email
    @Column(unique = true)
    private String email;

    @OneToMany(mappedBy = "user", orphanRemoval = true, fetch = FetchType.LAZY)
    @JsonManagedReference(value = "addresses")
    private Set<Address> addresses;
}

@Entity
@Table(name = "addresses")
public class Address {
    @Id
    @GeneratedValue
    private Long id;

    @NotBlank
    @ColumnTransformer(
            read = "PGP_SYM_DECRYPT(title::bytea, current_setting('my.dbsecretkey'))",
            write = "PGP_SYM_ENCRYPT (?, current_setting('my.dbsecretkey'))"
    )
    private String title;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "owner_email", referencedColumnName = "email")
    @JsonBackReference(value = "addresses")
    private User user;

    @Column(name = "owner_email", insertable=false, updatable=false)
    private String owner_email;

    @ElementCollection
    private Set<String> sharedWithUsers;
}

I want to update user's email like so

public void changeEmail(String email, String password, String newEmail) throws AuthenticationException {
    try {
        authenticationManager.authenticate(new UsernamePasswordAuthenticationToken(email, password));

        var user = userRepository.findByEmail(email).orElseThrow(() -> new ResponseStatusException(HttpStatus.NOT_FOUND));
        
        user.setEmail(newEmail);
        userRepository.save(user);
    } catch (BadCredentialsException e) {
        throw new ResponseStatusException(HttpStatus.FORBIDDEN);
    }
}

But when I do it I get this error: org.postgresql.util.PSQLException: ERROR: update or delete on table "users" violates foreign key constraint "fkoixwfhgxuda232r3m5llu7few" on table "addresses" Detail: Key (email)=(first@mail.com) is still referenced from table "addresses".

I've already tried to add CascadeType.ALL to User's entity, but it does not helped. Please help me.

Answer 1

The error says that the email from the table 'users' is also used as a foreign key in the table 'addresses'. This has been modeled in the entity Address with the annotation:

@JoinColumn(name = "owner_email", referencedColumnName = "email")

This has been done for reason. As a check of data integrity - if the user changes his/her email then the corresponding entry 'owner_email' should be changed as well.

Solution - update both fields in your service class before calling save. Like this:

user.setEmail(newEmail);
for (String address : user.getAddresses()) {
    address.setOwnerEmail(newEmail);
}
userRepository.save(user);