mysql unique index로 중복데이터 관리하기

ERD 설계를 하면서 중복데이터 관리는 어떻게 해야하는지 질문을 받았는데 문뜩 기억이 잘 나지 않아서 당황을 하다가
번뜩 조합키를 사용하면 되지 않을까라고 생각해보았지만 조합키? 오랜만에 해서 값자기 헤깔리기 시작했습니다....
역시 오랫동안 안쓰다보면 다 까먹기 마련인가봅니다. ^^;;

다시 검색을해서 unique index를 찾게되었고 PK는 기본적으로 unique하게 사용되는데 PK도 포함되서 중복 된 항목을보나..??
그러면 PK는 항상 auto increment를 하니까 중복 될 일이 없지 않은가?
그렇다면 PK를 제외하고도 원하는 컬럼만 묶어서 유니크하게 중복 데이터 등록을 방지 할 수 있을 것이다라고 생각하였고
그 과정을 아래 절차로 확인해보았습니다.

[환경]

DB : mysql

[테이블 생성 SQL]

CREATE TABLE `tb_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(50) DEFAULT NULL,
  `user_nm` varchar(250) DEFAULT NULL,
  `addr` varchar(500) DEFAULT NULL,
  `cell_phone` varchar(250) DEFAULT NULL,
  `agree_info` varchar(50) DEFAULT NULL,
  `birth_dt` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=UTF8

[중복데이터 등록 후 조회]

[등록]
mysql> insert into tb_user (user_id, user_nm) values ('user1', 'user1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_user (user_id, user_nm) values ('user1', 'user1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_user (user_id, user_nm) values ('user1', 'user1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_user (user_id, user_nm) values ('user1', 'user1');
Query OK, 1 row affected (0.00 sec)

[조회]
mysql> select * from tb_user;
+----+---------+---------+------+------------+------------+----------+
| id | user_id | user_nm | addr | cell_phone | agree_info | birth_dt |
+----+---------+---------+------+------------+------------+----------+
|  4 | user1   | user1   | NULL | NULL       | NULL       | NULL     |
|  6 | user1   | user1   | NULL | NULL       | NULL       | NULL     |
|  7 | user1   | user1   | NULL | NULL       | NULL       | NULL     |
|  8 | user1   | user1   | NULL | NULL       | NULL       | NULL     |
+----+---------+---------+------+------------+------------+----------+

위와 같이 중복 된 데이터가 insert가 잘되었다.
이제 중복 된 데이터가 insert가 되지 않도록 구성해보겠습니다.

[UNIQUE INDEX 생성]

여기서 의문인 것은 PK값인 "id" 항목도 같이 유니크 해야하는가이다.
우선 결론부터는 아니다이다
아래 와 같이 user_Id와 user_nm 두 개의 컬럼으로 unique index를 생성해보겠습니다.

alter table tb_user add unique index (user_id, user_nm);

 

그리고
insert 된 데이터를 모두 지우고

delete from tb_user where user_id = 'user1';

다시 insert 해보겠습니다.
첫번째 시도엔 정상적으로 데이터 insert가 수행되었습니다.

mysql> insert into tb_user (user_id, user_nm) values ('user1', 'user1');
Query OK, 1 row affected (0.00 sec)

두번째 시도엔 오류를 내밷습니다. 

mysql> insert into tb_user (user_id, user_nm) values ('user1', 'user1');
ERROR 1062 (23000): Duplicate entry 'user1-user1' for key 'tb_user.user_id'

[결론]

PK키인 id값은 auto increment되어 항상 다른 값이 insert되지 않지만 
별도로 설정한 unique index 조건인 user_id와 user_nm 두 가지의 조건이있다면
원하는 컬럼에 해당되는 중복 된 데이터 등록을 방지 할 수 있습니다.