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

    Моисеенко С.И. (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 John
2 Smith
3 John
4 Smith
5 Smith
6 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 blue
1 red
2 green
2 red
3 red
4 blue
6 red

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

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

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

id    name    color   
1 John blue
1 John red
2 Smith green
2 Smith red
3 John red
4 Smith blue
6 Tom red

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

1 John red
3 John red

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

4 Smith blue

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

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

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

Обновление таблицы T_details

Запрос

SELECT id_pk, name, color
    , RANK() OVER(PARTITION BY name, color ORDER BY name, color, id_pk) dup
    ,(SELECT MIN(id)  FROM T_pk WHERE T_pk.name = X.name) min_id
FROM T_pk X JOIN T_details ON id=id_pk;

определяет наличие дубликатов (значение dup > 1) и минимальное значение id в группе одинаковых имен (min_id). Вот результат выполнения этого запроса:

id_pk   name    color   dup  min_id   
1 John blue 1 1
1 John red 1 1
3 John red 2 1
4 Smith blue 1 2
2 Smith green 1 2
2 Smith red 1 2
6 Tom red 1 6

Теперь нам нужно заменить значение id_pk значением min_pk для всех строк, кроме третьей, т.к. эта строка есть дубликат второй строки, о чем говорит значение dup=2. Запрос на обновление можно написать так:

UPDATE T_details
SET id_pk=min_id
FROM T_details T_d JOIN (
  SELECT id_pk, name, color
      , RANK() OVER(PARTITION BY name, color ORDER BY name, color, id_pk) dup
      ,(SELECT MIN(id)  FROM T_pk WHERE T_pk.name = X.name) min_id
  FROM T_pk X JOIN T_details ON id=id_pk
      ) Y ON Y.id_pk=T_d.id_pk
WHERE dup =1;

После обновления таблица T_details примет следующий вид:

id_pk   color
1 blue
1 red
2 blue
2 green
2 red
3 red
6 red

Как видно, осталась одна лишняя дубликатная строка:

3 red

Но о ней можно не беспокоиться, так она будет удалена автоматически при каскадном удалении дубликатов из таблицы T_pk:

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

Последний запрос и является вторым этапом процедуры, в результате выполнения которого мы получим:

Таблица T_pk
id    name  
1 John
2 Smith
6 Tom

Таблица T_details
id_pk   color 
1 blue
1 red
2 blue
2 green
2 red
6 red

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

ALTER TABLE T_pk
ADD CONSTRAINT unique_name UNIQUE(name);

Dzone.com