처음 테이블을 만들 때 키를 제대로 안 잡아 두면, 나중에 같은 데이터가 두 번 들어가는 걸 막고 싶어도 막을 장치가 없습니다. 운영 중인 테이블에 고유 키를 추가하는 방법을 찾다가 Stack Overflow에 여러 답이 모여 있는 질문을 봤는데, 상황별로 쓸 만한 문장을 정리해 둡니다.

행마다 번호를 붙이는 기본 키 추가

테이블에 행을 구분할 키 자체가 없다면 자동 증가 컬럼을 하나 추가하면서 기본 키로 지정합니다. 기존 행에도 1부터 번호가 차례로 매겨집니다.

광고
ALTER TABLE TABLE_NAME ADD Id INT AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE MyTable ADD MyId INT AUTO_INCREMENT PRIMARY KEY;

새 컬럼을 맨 앞에 두고 싶다면 FIRST를 붙이고, 컬럼 추가와 기본 키 지정을 나눠 쓸 수도 있습니다.

ALTER TABLE `database_name`.`table_name`
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (`id`);

이미 기본 키가 있는 테이블에 또 PRIMARY KEY를 추가하면 "Multiple primary key defined" 오류가 나니, 먼저 SHOW CREATE TABLE로 구조를 확인해 보세요.

특정 컬럼에 중복을 막는 UNIQUE 추가

컬럼 하나가 중복되면 안 될 때는 이렇게 씁니다.

ALTER TABLE Table_Name ADD UNIQUE (column_name);
ALTER TABLE  0_value_addition_setup ADD UNIQUE (`value_code`);

여러 컬럼의 조합이 중복되지 않아야 할 때는 제약 이름을 붙여 묶습니다. 예를 들어 같은 사용자·같은 게임·같은 날짜·시각의 기록이 두 번 들어가면 안 되는 경우입니다.

ALTER TABLE TABLE_NAME ADD CONSTRAINT constr_ID UNIQUE (user_id, game_id, date, time);
ALTER TABLE table_name
ADD CONSTRAINT UC_table_name UNIQUE (field1,field2);

제가 찾던 게 바로 이 형태였습니다. 일반형으로 적어 두면 다음과 같습니다.

ALTER TABLE TABLE_NAME ADD CONSTRAINT KEY_NAME UNIQUE (COL_NAME1, COL_NAME2, COL_NAME3)

제약 이름(KEY_NAME)을 지정해 두면 나중에 ALTER TABLE TABLE_NAME DROP INDEX KEY_NAME 으로 지우거나 오류 메시지에서 어느 제약에 걸렸는지 알아보기 쉽습니다.

이미 중복이 있는 테이블이라면

UNIQUE를 추가하는 순간 MySQL은 기존 데이터를 모두 검사합니다. 중복된 행이 하나라도 있으면 "Duplicate entry" 오류와 함께 추가가 실패하니, 먼저 GROUP BY 대상 컬럼들로 묶어 HAVING COUNT(*) > 1 조건으로 중복을 찾아 정리해야 합니다. 행이 많은 테이블은 ALTER가 꽤 오래 걸릴 수 있어서, 운영 DB라면 사용량이 적은 시간에 백업을 떠 두고 실행하는 편이 안전합니다.

위의 네 컬럼 예제라면 중복 확인 쿼리는 이렇게 됩니다. date, time 은 함수 이름과 겹쳐서 헷갈리니 백틱으로 감싸 두는 게 좋습니다.

SELECT user_id, game_id, `date`, `time`, COUNT(*) AS cnt
FROM TABLE_NAME
GROUP BY user_id, game_id, `date`, `time`
HAVING COUNT(*) > 1;

중복이 많아서 손으로 지우기 어렵다면, 앞에서 자동 증가 Id 를 먼저 붙여 둔 뒤 같은 조합 중 번호가 작은 행 하나만 남기고 지우는 방법이 있습니다. 실행 전에 같은 조건으로 SELECT 를 돌려 지워질 행을 꼭 확인하세요.

DELETE t1 FROM TABLE_NAME t1
JOIN TABLE_NAME t2
  ON t1.user_id = t2.user_id AND t1.game_id = t2.game_id
 AND t1.`date` = t2.`date` AND t1.`time` = t2.`time`
 AND t1.Id > t2.Id;

예전 자료에는 ALTER IGNORE TABLE ... ADD UNIQUE 로 중복을 알아서 버리며 키를 거는 방법이 나오는데, 이 문법은 MySQL 5.7에서 없어졌습니다. 최근 버전에서는 문법 오류가 나니 위처럼 정리한 뒤 추가해야 합니다.

UNIQUE를 걸었는데도 중복이 들어오는 경우

조합에 들어간 컬럼 중 하나라도 NULL 이면 UNIQUE는 그 행을 막지 않습니다. MySQL에서 NULL 은 서로 같은 값으로 보지 않기 때문에, (1, 5, NULL) 같은 행은 몇 번이고 들어갈 수 있습니다. 중복을 확실히 막아야 하는 컬럼이라면 NOT NULL 로 바꾸고 빈 값 대신 쓸 기본값을 정해 두는 편이 낫습니다.

문자열 컬럼에 UNIQUE를 걸 때는 길이도 봐야 합니다. utf8mb4 컬럼은 한 글자를 4바이트로 계산하므로, 긴 VARCHAR 여러 개를 묶으면 "Specified key was too long" 오류가 날 수 있습니다. 인덱스 길이 한도는 MySQL 버전과 행 형식에 따라 달라서, 이 오류가 나면 컬럼 길이를 줄이거나 조합에서 긴 컬럼을 빼는 쪽으로 다시 설계해야 합니다. 그리고 기본 콜레이션(_ci)에서는 대소문자만 다른 'ABC' 와 'abc' 도 중복으로 막힌다는 점도 기억해 두세요.

UNIQUE를 걸어 두면 이후에는 INSERT ... ON DUPLICATE KEY UPDATE 나 INSERT IGNORE 를 쓸 수 있게 되어, 프로그램 쪽에서 "있으면 수정, 없으면 추가" 로직을 따로 짤 필요가 줄어듭니다.

참고: How add unique key to existing table (with non uniques rows) - Stack Overflow