Удаление повторяющихся строк из таблицы в SQL Server

Переводы статьи Переводы статьи
Код статьи: 139444 - Vizualiza?i produsele pentru care se aplic? acest articol.
Развернуть все | Свернуть все

Аннотация

Таблицы Microsoft SQL Server никогда не должны содержать повторяющихся строк и неуникальных первичных ключей. Ради краткости в этой статье первичные ключи иногда называются просто ключами. Повторяющиеся ключи нарушают целостность объектов и должны быть запрещены в реляционной системе. SQL Server содержит ряд механизмов, обеспечивающих целостность объектов, включая индексы, ограничения UNIQUE, ограничения PRIMARY KEY и триггеры.

Несмотря на это, в необычных обстоятельствах дублирование первичных ключей все же возможно, и от него необходимо избавиться. Дублирование первичных ключей возможно, например, если повторяющиеся первичные ключи имеются в нереляционных данных вне SQL Server и осуществляется импорт данных без обеспечения уникальности первичных ключей. Это также возможно, если при проектировании базы данных была допущена ошибка, например, если не обеспечивается целостность объектов для каждой таблицы.

Повторяющиеся ключи часто обнаруживаются при попытке создать уникальный индекс. Если они имеются, эта операция прерывается. При этом выводится следующее сообщение.
Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key.
Если используется SQL Server 2000 или SQL Server 2005, может быть выведено следующее сообщение об ошибке.
Сообщение 1505, уровень 16, состояние 1 Операция CREATE UNIQUE INDEX прервана, так как обнаружен повторяющийся ключ для имени объекта "%1!s!" и имени индекса "%2!s!". Повторяющееся значение ключа: %ls.
В данной статье рассматривается процедура обнаружения и удаления из таблицы повторяющихся первичных ключей. Однако при обнаружении повторяющихся ключей следует также тщательно изучить процедуру их появления, чтобы предотвратить возникновение этой проблемы в будущем.

Дополнительная информация

Проблема дублирования ключей будет рассмотрена на примере следующей таблицы с повторяющимся первичным ключом. В данной таблице первичным ключом являются два столбца (col1, col2). Создать уникальный индекс или ограничение 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
				
Этот код возвращает одну строку для каждого набора повторяющихся значений первичного ключа в таблице. Последний столбец в полученном результате представляет число дубликатов определенного значения первичного ключа.

Свернуть эту таблицуРазвернуть эту таблицу
col1col2
112


Если повторяющихся значений первичного ключа мало, лучше всего удалить их по отдельности вручную, например:
set rowcount 1
delete from t1
where col1=1 and col2=1
				
Значение rowcount должно быть на единицу меньше, чем число дубликатов определенного значения ключа. В данном примере дубликатов два, поэтому переменной rowcount присваивается значение 1. Значения col1 и col2 берутся из результата выполнения запроса GROUP BY, приведенного выше. Если запрос GROUP BY возвращает несколько строк, запрос «set rowcount» нужно будет выполнить по одному разу для каждой из этих строк. Переменной rowcount при каждом запуске нужно должно присваиваться значение на единицу меньше числа дубликатов определенного значения первичного ключа.

Перед удалением строки следует убедиться в том, что продублирована вся строка. Хотя это и маловероятно, значения первичного ключа могут быть продублированы, в то время как вся строка — нет. В качестве примера можно привести таблицу с номером социального обеспечения в качестве первичного ключа и двумя разными строками (представляющими людей) с уникальными атрибутами и одинаковым номером. В этом случае проблема, приведшая к дублированию ключа, могла также привести к записи в строку правильных уникальных данных. Перед удалением этих данных их следует скопировать и сохранить для анализа и, возможно, согласования.

При наличии в таблице большого числа разных наборов повторяющихся значений первичного ключа на их удаление вручную может потребоваться слишком много времени. В этом случае можно воспользоваться следующей процедурой.
  1. Сначала выполните приведенный выше запрос GROUP BY, чтобы узнать количество наборов повторяющихся значений первичного ключа и число дубликатов в каждом наборе.
  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 должна содержать уникальные первичные ключи, однако это условие не будет выполнено, если таблица t1 включает повторяющиеся первичные ключи и уникальные строки (как в приведенном выше примере с номерами социального обеспечения). Убедитесь в том, что каждый ключ в таблице holddups уникален и что у вас нет повторяющихся ключей при наличии уникальных строк. Если это так, подумайте, какие из строк нужно сохранить для определенного повторяющегося значения ключа. Например, запрос
    SELECT col1, col2, count(*)
    FROM holddups
    GROUP BY col1, col2
    						
    должен вернуть для каждой строки значение счетчика, равное 1. Если это так, перейдите к действию 5. Если нет, повторяющиеся ключи имеются при наличии уникальных строк, и тогда нужно решить, какие строки следует сохранить. Как правило, при этом или строка удаляется, или для нее создается уникальное значение ключа. Сделайте или первое, или второе для каждого такого повторяющегося ключа в таблице holddups.
  5. Удалите повторяющиеся строки из исходной таблицы, например:
    DELETE t1
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
    					
  6. Поместите уникальные строки обратно в исходную таблицу, например:
    INSERT t1 SELECT * FROM holddups
    					

Свойства

Код статьи: 139444 - Последний отзыв: 18 февраля 2011 г. - Revision: 5.0
Информация в данной статье относится к следующим продуктам.
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Ключевые слова: 
kbsqlmanagementtools kbinfo kbusage KB139444

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com