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

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

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

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

Итак, пусть имеется таблица с первичным ключом 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