Question 89075 in PostgreSQL Question posted in
The official documentation can be found here.

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

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)=([email protected]) 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.

Tags:

2

Answers


  1. 0

    i have update one line of code in your changeEmail method go and try, it will work

    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);
    
    for (Address address : user.getAddresses()) {
        address.setUser(null);
    }
    
    userRepository.save(user);
} catch (BadCredentialsException e) {
    throw new ResponseStatusException(HttpStatus.FORBIDDEN);
}

    }

    if you are deleting the user you can add the @OnDelete in your user entity

    @OneToMany(mappedBy = "user", orphanRemoval = true, fetch = FetchType.LAZY)
@JsonManagedReference(value = "addresses")
@OnDelete(action = OnDeleteAction.CASCADE)
private Set<Address> addresses;
    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search