프로젝트를 진행하다 복합키를 설정해야 하는 경우가 생겼서 Id Class를 이용해 복합키를 생성하였는데, Insert 후 똑같은 구문을 Insert 할 경우 duplicate error 가 발생하여야 하는데, update 구문이 나갔다. 이를 해결?은 아니고 그냥 다른 방법으로 대체하였는데, 이를 알아보자.

현재 상태

Entity

코드는 이렇다.

@Entity
@Getter
@NoArgsConstructor
@IdClass(PlayMatchId.class)
public class PlayMatch {

    @Id
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "player_id")
    private Player player_id;

    @Id
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "match_id")
    private MatchDay matchDay;

    @Column(name = "goals")
    @ColumnDefault("0")
    private int goals;

    @Column(name = "assists")
    @ColumnDefault("0")
    private int assists;


    @Builder
    public PlayMatch(Player player, MatchDay matchDay) {
        this.player_id = player;
        this.matchDay = matchDay;
        this.goals = 0;
        this.assists = 0;
    }

    public void updateGoals(int goals) {
        this.goals = goals;
    }

    public void updateAssists(int assists) {
        this.assists = assists;
    }

    public void setPlayer_id(Player player) {
        this.player_id = player;
    }

    public void setMatchDay(MatchDay matchDay) {
        this.matchDay = matchDay;
    }
}

쿼리문

Hibernate: create table match_day (match_id bigint not null auto_increment, away_goals integer default 0 not null, away_name varchar(255), goals integer default 0 not null, match_date datetime(6), state varchar(255), primary key (match_id)) engine=InnoDB
Hibernate: create table player (player_id bigint not null auto_increment, appearances integer default 0 not null, assists integer default 0 not null, back_num integer, birth_date date, description longtext, goals integer default 0 not null, height integer not null, image_url longtext, player_name varchar(255) not null, position varchar(255), weight integer not null, primary key (player_id)) engine=InnoDB
Hibernate: create table play_match (match_id bigint not null, player_id bigint not null, assists integer default 0, goals integer default 0, primary key (match_id, player_id)) engine=InnoDB
Hibernate: alter table player add constraint UK_nvmkd35itx0ibm2btbu3naluk unique (back_num)
Hibernate: alter table play_match add constraint FK5dljrm6d9y05ispdkm5r03rfx foreign key (match_id) references match_day (match_id)
Hibernate: alter table play_match add constraint FK8cl22xt318la2xc209c04q5en foreign key (player_id) references player (player_id)

Hibernate에서 table을 생성할 때, play_match table에서 PK와 FK로 match_id, player_id를 사용하는 것을 쿼리문에서 확인할 수 있다.

문제 발생

현재 디비 상태

select * from player;
+-----------+-------------+---------+----------+------------+-------------+-------+--------+-----------+-------------+----------+--------+
| player_id | appearances | assists | back_num | birth_date | description | goals | height | image_url | player_name | position | weight |
+-----------+-------------+---------+----------+------------+-------------+-------+--------+-----------+-------------+----------+--------+
|         1 |           0 |       0 |        9 | NULL       | NULL        |     0 |    170 | NULL      | 양원영      | FW       |     69 |
|         2 |           0 |       0 |        6 | NULL       | NULL        |     0 |    178 | NULL      | 정형우      | DF       |     74 |
|         3 |           0 |       0 |       30 | NULL       | NULL        |     0 |    170 | NULL      | 양원영      | DF       |     69 |
+-----------+-------------+---------+----------+------------+-------------+-------+--------+-----------+-------------+----------+--------+



select * from match_day;
+----------+------------+--------------+-------+----------------------------+--------+
| match_id | away_goals | away_name    | goals | match_date                 | state  |
+----------+------------+--------------+-------+----------------------------+--------+
|        1 |          0 | 무지막지     |     0 | 2022-09-29 18:00:00.000000 | BEFORE |
+----------+------------+--------------+-------+----------------------------+--------+



MariaDB [spring_test]> select * from play_match;
+----------+-----------+---------+-------+
| match_id | player_id | assists | goals |
+----------+-----------+---------+-------+
|        1 |         1 |       0 |     1 |
+----------+-----------+---------+-------+

현재 DB가 이런 상황에서, match_id=1, player_id=1인 Data는 play_match에 삽입이 될 수 없다.

그러나 api로 해당 부분을 날려보면

성공적으로 추가 되었다고 되며,

Hibernate: select player0_.player_id as player_i1_1_0_, player0_.appearances as appearan2_1_0_, player0_.assists as assists3_1_0_, player0_.back_num as back_num4_1_0_, player0_.birth_date as birth_da5_1_0_, player0_.description as descript6_1_0_, player0_.goals as goals7_1_0_, player0_.height as height8_1_0_, player0_.image_url as image_ur9_1_0_, player0_.player_name as player_10_1_0_, player0_.position as positio11_1_0_, player0_.weight as weight12_1_0_ from player player0_ where player0_.player_id=?
Hibernate: select matchday0_.match_id as match_id1_0_0_, matchday0_.away_goals as away_goa2_0_0_, matchday0_.away_name as away_nam3_0_0_, matchday0_.goals as goals4_0_0_, matchday0_.match_date as match_da5_0_0_, matchday0_.state as state6_0_0_ from match_day matchday0_ where matchday0_.match_id=?
Hibernate: select playmatch0_.match_id as match_id1_2_0_, playmatch0_.player_id as player_i2_2_0_, playmatch0_.assists as assists3_2_0_, playmatch0_.goals as goals4_2_0_ from play_match playmatch0_ where playmatch0_.match_id=? and playmatch0_.player_id=?
Hibernate: update play_match set assists=?, goals=? where match_id=? and player_id=?

쿼리문도 정상적으로 나가고(심지어 update로 나간다.)

select * from play_match;
+----------+-----------+---------+-------+
| match_id | player_id | assists | goals |
+----------+-----------+---------+-------+
|        1 |         1 |       0 |     0 |
+----------+-----------+---------+-------+

확인해보면 update가 된 것을 확인할 수 있다. 왜 이런 현상이 발생하는 것일까?(원래는 pk가 이미 존재하므로 아예 삽입이 되면 안된다. JpaRepository의 save를 이용하기 때문)

StackOverflow등 여러 사이트를 뒤져 본 결과, 삽입하기 전에 미리 확인하라고 하는 것 같다.(아니면 내가 연관관계를 잘못 짠 것일지도..)

Unique Key를 사용하여 해결

그래서 그렇게 하는 것보단 그냥 unique key 처리를 하는 게 낫다 싶어서, https://jwkim96.tistory.com/166 링크를 보니 pk는 따로 두고 unique key를 설정하는 것이 유지 보수가 낫다고 한다.

그래서 UNIQUE KEY로 바꿔주고

@Entity
@Table(
        uniqueConstraints = {
                @UniqueConstraint(
                        name = "constraintName",
                        columnNames = {"player_id", "match_id"}
                )
        }
)
@Getter
@NoArgsConstructor
public class PlayMatch {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "player_id")
    private Player player;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "match_id")
    private MatchDay matchDay;

    @Column(name = "goals")
    @ColumnDefault("0")
    private int goals;

    @Column(name = "assists")
    @ColumnDefault("0")
    private int assists;


    @Builder
    public PlayMatch(Player player, MatchDay matchDay) {
        this.player = player;
        this.matchDay = matchDay;
        this.goals = 0;
        this.assists = 0;
    }

    public void updateGoals(int goals) {
        this.goals = goals;
    }

    public void updateAssists(int assists) {
        this.assists = assists;
    }

    public void setPlayer_id(Player player) {
        this.player = player;
    }

    public void setMatchDay(MatchDay matchDay) {
        this.matchDay = matchDay;
    }
}

삽입을 해보았더니

java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '1-1' for key 'constraintName'

에러가 나오면서 삽입이 되지 않고 정상작동한다.

원인을 좀 더 생각해보고싶은데, 자료를 못찾겠다.

혹시 지적사항이나 다른 방안이 있으면 댓글로 알려주시면 감사하겠습니다.