错误:更新或删除表"users"违反外键约束';fkoixwfhgxuda232r3m5llu7fe

Question

我有两个实体Users和Addresses，它们具有来自Users的OneToMany映射。

@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;
}

我想更新用户的电子邮件，像这样

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);
}
}

但是当我这样做的时候，我得到了这个错误:org.postgresql.util.PSQLException: ERROR: update or delete on table "users" violates foreign key constraint "fkoixwfhgxuda232r3m5llu7few" on table "addresses" Detail: Key (email)=([e-mail]) is still referenced from table "addresses".

我已经尝试添加cascadtype。所有到用户的实体，但它没有帮助。请帮帮我。

Answer 1

错误提示来自'users'表的电子邮件也被用作'addresses'表中的外键。这已经在实体Address中建模，注释为:

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

这样做是有原因的。作为数据完整性的检查-如果用户更改了他/她的电子邮件，那么相应的条目'owner_email'也应该更改。

<<p>解决方案/strong>-在调用save之前更新服务类中的两个字段。这样的:

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

Answer 2

我已经在你的changeEmail方法中更新了一行代码去试试，它会工作的

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);
}

}

如果你要删除用户，你可以在你的用户实体中添加@OnDelete

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