0

I've the following problem with my application.

Definition: I have 3 tables. User, Password and Confirmation Token. User table is the parent table. Password and confirmation token tables are the children tables that have one to one relationship with the user table. User and Confirmation table ids are stored in User table as foreign keys.

Problem:

When user activate his account, the confirmation token row should be removed from confirmation token table. Consequetively, the column in the user table should as well be removed.

What I achieve: With the following scheme, what happens is that, when I remove the confirmation token, the whole user row data is deleted as well but password row data is not deleted. All i want is that, when confirmation token is removed, only the corresponding row and the foreign key column from the user table should be deleted.

public class User {

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 private int userId;

 private String username;
 private String email;
 private UserStatus status;
 private LocalDateTime registerDate;
 private LocalDateTime lastLoginDate;

 @OneToOne(fetch = FetchType.LAZY)
 @JoinColumn(name = "PASSWORD_ID", referencedColumnName = "passwordId")
 private UserPasswords userPassword;

 @OneToOne(fetch = FetchType.LAZY)
 @JoinColumn(name = "ACCOUNT_CONFIRMATION_TOKEN_ID")
 private ConfirmationToken confirmationToken;}



@Entity
public class ConfirmationToken {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int tokenId;
private String confirmationToken;
private LocalDateTime creationDate;

  @OneToOne(fetch = FetchType.LAZY, mappedBy = "confirmationToken",cascade=CascadeType.REMOVE,     optional=true)
  private User user;

}

 @Entity
 @Table(name = "USER_PASSWORDS")
 public class UserPasswords {

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 private int passwordId;

 private String password;
 private String beforePassword;
 private int wrongPasswordTrial;
 private LocalDateTime wrongPasswordDate;
 private LocalDateTime lastPasswordUpdate;

 @OneToOne(fetch = FetchType.LAZY, mappedBy = "userPassword")
 private User user;
Ahmet Eroğlu
  • 594
  • 1
  • 7
  • 23

1 Answers1

0

That happens because cascade=CascadeType.REMOVE was set. This sets User entity be deleted when the corresponding ConfirmationToken row is deleted.

Deleting just the value of a column can not be done with a DELETE statement. This can only be done with an UPDATE statement. DELETE will always remove the entire row.

To achieve what you want, you can move the join column to ConfirmationToken table instead, by moving the @JoinColumn annotation to the mapping in ConfirmationToken entity. Here:

@OneToOne(fetch = FetchType.LAZY, mappedBy = "confirmationToken", optional=true)
@JoinColumn(name = "ACCOUNT_CONFIRMATION_TOKEN_ID")
private User user;

This will move the column you want to remove to the table that will have the entire row removed. This discards the need for an UPDATE statement altogether.

dddkirby
  • 3
  • 2
  • Hey thanks for the swift comment. Does that mean that i should get the user instance and set the confirmation_token_id foreign key to null and then also remove the confirmation token instance using confirmation token repository interface provided by crud interface – Ahmet Eroğlu Apr 07 '21 at 07:58
  • If you still want to go for this approach, yes. Then you will end up with 2 SQL statements being executed, and I am not sure, but I think you'll need to call save() for the user as an extra line of code. – dddkirby Apr 07 '21 at 10:32
  • Do you have any other suggestions for a better appoach? with regards to managing this relationship. ? So each user will have a confirmation token upon registering to the site. Once they are activated via mail, this token should be deleted. So what i did was to put foreign key into users table, and do what i wrote above – Ahmet Eroğlu Apr 07 '21 at 12:40