테이블에서 중복 행을 제거하는 방법

Microsoft SQL Server 테이블에는 중복 행이나 고유하지 않은 기본 키가 없어야 합니다. 본 문서에서는 기본 키를 간략히 "키" 또는 "PK"로 지칭하기도 합니다. 그렇다고 해서 "키" 또는 "PK"가 반드시 "기본 키"를 나타내는 것은 아닙니다. 중복 PK는 엔티티 무결성을 위반하는 것이며 관계형 시스템에서는 허용되지 않습니다. SQL Server에는 엔티티 무결성을 유지하기 위한 인덱스, UNIQUE 제약 조건, PRIMARY KEY 제약 조건, 트리거 등의 다양한 메커니즘이 포함되어 있습니다.
그럼에도 불구하고 중복된 기본 키가 발생하는 예외적 경우가 있습니다. 이런 경우에는 중복된 기본 키를 제거해야 합니다. 중복 PK가 SQL Server 외부의 비관계형 데이터에 존재하고 PK 고유성이 항상 유지되도록 설정되지 않은 상태에서 데이터를 들여올 경우가 여기에 해당합니다. 각 테이블에서의 엔티티 무결성이 항상 유지되도록 설정하지 않는 등의 데이터베이스 디자인 오류로 인해서도 중복된 기본 키가 발생합니다.
대개 고유 인덱스를 만들려고 할 때 중복 PK가 발견되는데, 이런 경우에는 다음 메시지와 함께 작업이 중단됩니다. 이 메시지는 다음과 같습니다.

Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key.

본 문서에서는 테이블에서 중복된 기본 키를 찾고 이를 제거하는 방법을 설명합니다. 그러나 무엇 보다도 중요한 것은 중복을 발생시킨 과정을 면밀히 검사하여 재발을 방지하는 것입니다.

추가 정보

여기서는 중복 PK 값이 있는 다음 테이블을 예로 사용합니다. 이 테이블에서 기본 키는 두 열(col1, col2)입니다. 두 행에 중복 PK가 있기 때문에 고유 인덱스나 PRIMARY KEY 제약 조건을 만들 수 없습니다. 다음은 중복된 기본 키를 확인하고 제거하는 절차입니다.

create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two') 

첫 단계는 중복된 기본 키 값이 있는 행을 확인하는 것입니다.

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

이는 테이블에서 확인된 각 중복 PK 값의 집합에 대해 행을 하나씩 반환합니다. 이 결과의 마지막 열은 특정 PK 값에 대한 중복 수입니다.

1

1

2

중복 PK 값의 집합이 많지 않은 경우 가장 좋은 방법은 이들 집합을 수동으로 하나씩 삭제하는 것입니다. 예를 들면 다음과 같습니다.

set rowcount 1
delete from t1
where col1=1 and col2=1

rowcount 값은 주어진 키 값의 중복 수에서 1을 뺀 값(n-1)이어야 합니다. 이 예에서는 2개의 중복이 있으므로 rowcount가 1로 설정됩니다. col1/col2 값은 위의 GROUP BY 쿼리 결과에서 가져온 것입니다. GROUP BY 쿼리가 여러 행을 반환하는 경우 "set rowcount" 쿼리를 각 행에 대해 한 번씩 실행해야 합니다. 이를 실행할 때마다 rowcount를 특정 PK 값의 중복 수에서 1을 뺀 값(n-1)으로 설정합니다.

행을 삭제하기 전에 행 전체가 중복인지 확인해야 합니다. 가능성이 희박하기는 하지만 PK 값이 중복되고 행 전체는 중복되지 않을 수 있습니다. 이것의 예로 기본 키가 사원 번호인 테이블에 번호는 같지만 각각 고유한 속성을 갖는 두 명의 다른 사람(또는 행)이 있는 경우를 들 수 있습니다. 이러한 경우 중복 키로 인해 행에 유효한 고유 데이터가 온 것일 수도 있습니다. 이 데이터는 나중에 조사 및 조정할 수 있도록 삭제 전에 복사해 놓고 보존해야 합니다.

테이블에 서로 다른 중복 PK 값의 집합이 많은 경우 이들 집합을 하나씩 제거하는 것은 너무 시간 낭비일 수 있습니다. 이러한 경우 다음 절차를 사용할 수 있습니다.

1. 먼저, 위의 GROUP BY 쿼리를 실행하여 중복 PK 값의 집합 수와 각 집합의 중복 수를 파악합니다.
2. 중복 키 값을 선택하여 보관 테이블에 넣습니다. 예를 들면 다음과 같습니다.

   SELECT col1, col2, col3=count(*)
   INTO holdkey
   FROM t1
   GROUP BY col1, col2
   HAVING count(*) > 1

3. 중복 행을 선택하여 보관 테이블에 넣고 처리 중인 중복을 제거합니다. 예를 들면 다음과 같습니다.

   SELECT DISTINCT t1.*
   INTO holddups
   FROM t1, holdkey
   WHERE t1.col1 = holdkey.col1
   AND t1.col2 = holdkey.col2

4. 이 시점에서 holddups 테이블에 고유한 PK가 있어야 하지만 위의 SSN 예에서와 같이 t1에 중복 PK와 고유한 행이 동시에 있는 경우에는 이 테이블의 PK가 고유하지 않게 됩니다. holddups의 각 키가 고유한지, 중복 키와 고유 행이 동시에 존재하지 않는지 확인하십시오. 중복 키와 고유 행이 같이 존재하는 경우에는 일단 작업을 중단하고 주어진 중복 키 값을 보관할 행을 조정해야 합니다. 예를 들어, 아래 쿼리는

   SELECT col1, col2, count(*)
   FROM holddups
   GROUP BY col1, col2

   각 행에 대해 1의 개수를 반환해야 합니다. 쿼리가 각 행에 대해 1의 개수를 반환하면 아래의 5단계로 갑니다. 그렇지 않은 경우에는 중복 키와 고유 행이 같이 존재하는 것이므로 저장할 행을 결정해야 합니다. 이를 위해 대개 행을 삭제하거나 이 행에 대한 고유 키 값을 새로 만듭니다. holddups 테이블의 이러한 각 중복 PK에 대해 이들 절차 중 적절한 방법을 사용하십시오.
5. 원본 테이블에서 중복 행을 삭제합니다. 예를 들면 다음과 같습니다.

   DELETE t1
   FROM t1, holdkey
   WHERE t1.col1 = holdkey.col1
   AND t1.col2 = holdkey.col2

6. 고유 행을 다시 원래 테이블에 넣습니다. 예를 들면 다음과 같습니다.

   INSERT t1 SELECT * FROM holddups
   [출처] 테이블에서 중복 행을 제거하는 방법|작성자 진카자마