Как удалить дубликаты строк при наличии первичного ключа?

Моисеенко С.И. (25-07-2009)

В предыдущей статье мы рассмотрели решение проблемы с дубликатами, вызванной отсутствием первичного ключа. Рассмотрим теперь более тяжелый случай, когда ключ вроде бы есть, но он является синтетическим, что при неправильном проектировании тоже может привести к появлению дубликатов с точки зрения предметной области.

Странное дело, но, рассказывая на лекциях о недостатках синтетических ключей, я, тем не менее, постоянно сталкиваюсь с тем, что студенты в своих первых проектах с базами данных их неизменно используют. Видимо, в человеке заложена генетическая потребность все перенумеровывать, и помочь здесь может только психотерапевт. :-)

Я не хочу обсуждать здесь избитую проблему синтетических ключей. Скажу лишь, что если вы решили использовать их в качестве первичного ключа, то следует также создавать естественный уникальный ключ, чтобы избежать описанной ниже ситуации.

Итак, пусть имеется таблица с первичным ключом id и столбцом name, который в соответствии с ограничениями предметной области должен содержать уникальные значения. Однако если определить структуру таблицы следующим образом

CREATE TABLE T_pk (id INT IDENTITY PRIMARY KEY,

name VARCHAR(50));

то появлению дубликатов ничто не препятствует. Следовало бы использовать следующую структуру таблицы:

CREATE TABLE T_pk (id INT IDENTITY PRIMARY KEY,

name VARCHAR(50) UNIQUE);

Все знают, как нужно правильно поступить, однако зачастую приходится иметь с "унаследованной" структурой и данными, которые нарушают ограничения предметной области. Вот пример:

id name 1 John2 Smith3 John4 Smith5 Smith6 Tom

Вы можете спросить: "А чем эта проблема отличается от предыдущей? Ведь здесь есть даже более простое решение - просто удалить все строки из каждой группы с одинаковыми значениями в столбце name, оставив лишь строку с минимальным/максимальным значением id. Например, так:"

DELETE

FROM T_pk

WHERE id > (SELECT MIN(id) FROM T_pk X WHERE X.name = T_pk.name);

Правильно, но я вам еще не все рассказал. :-) Представьте, что у нас имеется дочерняя таблица T_details, связанная с таблицей T_pk по внешнему ключу:

CREATE TABLE T_details (id_pk INT FOREIGN KEY REFERENCES

T_pk ON DELETE CASCADE,

color VARCHAR(10),

PRIMARY KEY (id_pk, color);

Эта таблица может содержать такие данные:

id_pk color 1 blue1 red2 green2 red3 red4 blue6 red

Для большей наглядности воспользуемся запросом

SELECT id, name, color FROM T_pk JOIN T_details ON id= id_pk;

чтобы увидеть имена:

id name color1 John blue1 John red2 Smith green2 Smith red3 John red4 Smith blue6 Tom red

Таким образом, оказывается, что данные, фактически относящиеся к одному лицу, ошибочно оказались разнесенными по разным родительским записям. Кроме того, дубликаты оказались и в этой таблице:

1 John red3 John red

Очевидно, что подобные данные приведут к ошибочному анализу и отчетам. Более того, каскадное удаление приведет к потере данных. Например, если мы оставим только строки с минимальным идентификатором в каждой группе в таблице T_pk, то потеряем строку

4 Smith blue

в таблице T_details. Следовательно, мы должны при устранении дубликатов учитывать обе таблицы.

Процедуру "очистки" данных можно провести в два этапа:

§ Выполнить обновление таблицы T_details, приписав данные, относящиеся к одному имени, к id с минимальным номером в группе.

§ Удалить дубликаты из таблицы T_pk, оставив только строки с минимальным id в каждой группе с одинаковым значением в столбце name.