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

huangapple
go评论87阅读模式
英文:

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

问题

以下是代码的翻译部分：

  1. 我有两个实体Users和Addresses，它们之间有OneToMany的映射从Users到Addresses。
  3. ```java
  4. @Entity
  5. @Table(name = "users")
  6. public class User {
  7.     @Id
  8.     @GeneratedValue
  9.     private Long id;
  11.     @Email
  12.     @Column(unique = true)
  13.     private String email;
  15.     @OneToMany(mappedBy = "user", orphanRemoval = true, fetch = FetchType.LAZY)
  16.     @JsonManagedReference(value = "addresses")
  17.     private Set<Address> addresses;
  18. }
  1. @Entity
  2. @Table(name = "addresses")
  3. public class Address {
  4.     @Id
  5.     @GeneratedValue
  6.     private Long id;
  8.     @NotBlank
  9.     @ColumnTransformer(
  10.             read = "PGP_SYM_DECRYPT(title::bytea, current_setting('my.dbsecretkey'))",
  11.             write = "PGP_SYM_ENCRYPT (?, current_setting('my.dbsecretkey'))"
  12.     )
  13.     private String title;
  15.     @ManyToOne(fetch = FetchType.LAZY)
  16.     @JoinColumn(name = "owner_email", referencedColumnName = "email")
  17.     @JsonBackReference(value = "addresses")
  18.     private User user;
  20.     @Column(name = "owner_email", insertable=false, updatable=false)
  21.     private String owner_email;
  23.     @ElementCollection
  24.     private Set<String> sharedWithUsers;
  25. }

我想要更新用户的电子邮件，如下所示

  1. public void changeEmail(String email, String password, String newEmail) throws AuthenticationException {
  2.     try {
  3.         authenticationManager.authenticate(new UsernamePasswordAuthenticationToken(email, password));
  5.         var user = userRepository.findByEmail(email).orElseThrow(() -> new ResponseStatusException(HttpStatus.NOT_FOUND));
  6.         
  7.         user.setEmail(newEmail);
  8.         userRepository.save(user);
  9.     } catch (BadCredentialsException e) {
  10.         throw new ResponseStatusException(HttpStatus.FORBIDDEN);
  11.     }
  12. }

但当我这样做时，我遇到了这个错误：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".

我已经尝试向User实体添加CascadeType.ALL，但没有帮助。请帮助我。

英文:

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

  1. @Entity
  2. @Table(name = &quot;users&quot;)
  3. public class User {
  4.     @Id
  5.     @GeneratedValue
  6.     private Long id;
  8.     @Email
  9.     @Column(unique = true)
  10.     private String email;
  12.     @OneToMany(mappedBy = &quot;user&quot;, orphanRemoval = true, fetch = FetchType.LAZY)
  13.     @JsonManagedReference(value = &quot;addresses&quot;)
  14.     private Set&lt;Address&gt; addresses;
  15. }
  1. @Entity
  2. @Table(name = &quot;addresses&quot;)
  3. public class Address {
  4.     @Id
  5.     @GeneratedValue
  6.     private Long id;
  8.     @NotBlank
  9.     @ColumnTransformer(
  10.             read = &quot;PGP_SYM_DECRYPT(title::bytea, current_setting(&#39;my.dbsecretkey&#39;))&quot;,
  11.             write = &quot;PGP_SYM_ENCRYPT (?, current_setting(&#39;my.dbsecretkey&#39;))&quot;
  12.     )
  13.     private String title;
  15.     @ManyToOne(fetch = FetchType.LAZY)
  16.     @JoinColumn(name = &quot;owner_email&quot;, referencedColumnName = &quot;email&quot;)
  17.     @JsonBackReference(value = &quot;addresses&quot;)
  18.     private User user;
  20.     @Column(name = &quot;owner_email&quot;, insertable=false, updatable=false)
  21.     private String owner_email;
  23.     @ElementCollection
  24.     private Set&lt;String&gt; sharedWithUsers;
  25. }

I want to update user's email like so

  1. public void changeEmail(String email, String password, String newEmail) throws AuthenticationException {
  2. 	try {
  3. 		authenticationManager.authenticate(new UsernamePasswordAuthenticationToken(email, password));
  5. 		var user = userRepository.findByEmail(email).orElseThrow(() -&gt; new ResponseStatusException(HttpStatus.NOT_FOUND));
  6. 		
  7. 		user.setEmail(newEmail);
  8. 		userRepository.save(user);
  9. 	} catch (BadCredentialsException e) {
  10. 		throw new ResponseStatusException(HttpStatus.FORBIDDEN);
  11. 	}
  12. }

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

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

答案1

得分: 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:

  1. @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:

  1. user.setEmail(newEmail);
  2. for (String address : user.getAddresses()) {
  3.     address.setOwnerEmail(newEmail);
  4. }
  5. userRepository.save(user);
英文:

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:

  1. @JoinColumn(name = &quot;owner_email&quot;, referencedColumnName = &quot;email&quot;)

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:

  1. user.setEmail(newEmail);
  2. for (String address : user.getAddresses()) {
  3.     address.setOwnerEmail(newEmail);
  4. }
  5. userRepository.save(user);

答案2

得分: 0

I have updated one line of code in your changeEmail method. Here's the translated portion:

  1. public void changeEmail(String email, String password, String newEmail) throws AuthenticationException {
  2.     try {
  3.         authenticationManager.authenticate(new UsernamePasswordAuthenticationToken(email, password));
  5.         var user = userRepository.findByEmail(email).orElseThrow(() -&gt; new ResponseStatusException(HttpStatus.NOT_FOUND));
  7.         user.setEmail(newEmail);
  9.         for (Address address : user.getAddresses()) {
  10.             address.setUser(null);
  11.         }
  13.         userRepository.save(user);
  14.     } catch (BadCredentialsException e) {
  15.         throw new ResponseStatusException(HttpStatus.FORBIDDEN);
  16.     }
  17. }

If you are deleting the user, you can add the @OnDelete in your user entity:

  1. @OneToMany(mappedBy = &quot;user&quot;, orphanRemoval = true, fetch = FetchType.LAZY)
  2. @JsonManagedReference(value = &quot;addresses&quot;)
  3. @OnDelete(action = OnDeleteAction.CASCADE)
  4. private Set&lt;Address&gt; addresses;
英文:

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

  1. public void changeEmail(String email, String password, String newEmail) throws AuthenticationException {
  2. try {
  3.     authenticationManager.authenticate(new UsernamePasswordAuthenticationToken(email, password));
  5.     var user = userRepository.findByEmail(email).orElseThrow(() -&gt; new ResponseStatusException(HttpStatus.NOT_FOUND));
  6.     
  8.     user.setEmail(newEmail);
  9.     
  10.     for (Address address : user.getAddresses()) {
  11.         address.setUser(null);
  12.     }
  13.     
  14.     userRepository.save(user);
  15. } catch (BadCredentialsException e) {
  16.     throw new ResponseStatusException(HttpStatus.FORBIDDEN);
  17. }

}

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

  1. @OneToMany(mappedBy = &quot;user&quot;, orphanRemoval = true, fetch = FetchType.LAZY)
  2. @JsonManagedReference(value = &quot;addresses&quot;)
  3. @OnDelete(action = OnDeleteAction.CASCADE)
  4. private Set&lt;Address&gt; addresses;

huangapple
  • 本文由 发表于 2023年4月7日 01:00:27
  • 转载请务必保留本文链接：https://go.coder-hub.com/75952014.html
  • java
  • postgresql
  • spring
  • spring-data
  • spring-data-jpa
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定