Аннотация

Таблицы 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

Этот код возвращает одну строку для каждого набора повторяющихся значений первичного ключа в таблице. Последний столбец в полученном результате представляет число дубликатов определенного значения первичного ключа.

col1

col2

1

1

2



Если повторяющихся значений первичного ключа мало, лучше всего удалить их по отдельности вручную. например:

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

Нужна дополнительная помощь?

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединение к программе предварительной оценки Майкрософт

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?

Спасибо за ваш отзыв!

×