Как удалить дубликаты строк при наличии первичного ключа?
Моисеенко С.И. (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. Следовательно, мы должны при устранении дубликатов учитывать обе таблицы.
Процедуру "очистки" данных можно провести в два этапа:
- Выполнить обновление таблицы T_details, приписав данные, относящиеся к одному имени, к id с минимальным номером в группе.
- Удалить дубликаты из таблицы 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);
