Удаление записей, которые нарушают ссылочную целостность

Задача

Требуется удалить записи из таблицы, если они ссылаются на несуществующие записи другой таблицы. Пример: некоторые служащие приписаны к отделу, которого не существует. Необходимо удалить записи этих служащих.

Решение

Для проверки действительности номеров отделов используйте предикат NOT EXISTS с подзапросом:

delete from emp where not exists ( select * from dept where dept.deptno = emp.deptno

Или можно написать запрос с предикатом NOT IN:

delete from emp

where deptno not in (select deptno from dept)

Обсуждение

При удалении самое главное - правильно описать в предикате WHERE строки, подлежащие удалению.

В решении с NOT EXISTS используется связанный подзапрос для проверки существования в таблице DEPT записи, значение DEPTNO которой соответствует DEPTNO заданной записи таблицы EMP. Если такая запись есть, то запись EMP сохраняется. В противном случае она удаляется. Такая проверка проводится для всех записей таблицы EMP.

В решении с предикатом IN используется подзапрос для извлечения списка действительных номеров отделов. Затем значения столбца DEPTNO всех записей таблицы EMP сравниваются с этим списком. Если обнаруживается запись, DEPTNO которой не соответствует значениям списка, она удаляется из таблицы EMP.

Уничтожение дублирующихся записей Задача

Требуется уничтожить дублирующиеся записи из таблицы. Рассмотрим следующую таблицу:

create table dupes (id integer, name varchar(10))

insert into dupes values (1, NAPOLEON)

1 NAPOLEON

2 DYNAMITE

3 DYNAMITE

4 SHE SELLS

5 SEA SHELLS

6 SEA SHELLS

7 SEA SHELLS

Для каждой группы дублирующихся имен, таких как SEA SHELLS , необходимо сохранить один произвольно выбранный ID и уничтожить остальные. В случае с SEA SHELLS не важно, будут ли уничтожены записи с ID 5 и 6, или 5 и 7, или 6 и 7, но в итоге должна остаться всего одна запись для SEA SHELLS .

Решение

Организуйте произвольный выбор сохраняемого ID с помощью подзапроса с агрегатной функцией, такой как MIN (в данном случае в таблице остается только запись с минимальным значением ID).

1 delete from dupes

2 where id not in ( select min(id)

3 from dupes

4 group by name )

Обсуждение

Первое, что необходимо сделать при удалении дубликатов, - точно определить, какие две строки считаются дубликатами . Для моего примера в данном рецепте дубликат - это две записи, содержащие одинаковое значение в столбце NAME. Вооружившись этим определением, мы получаем множество дубликатов и уже выбираем другие столбцы как условие сохранения записей из этого набора. Лучше всего, если этим определяющим столбцом (или столбцами) будет первичный ключ. Я использовал столбец ID, потому что двух записей с одинаковым ID не существует.

Суть решения в том, что строки группируются по дублирующимся значениям (в данном случае, по значению столбца NAME), и затем с помощью агрегатной функции из группы выбирается всего одно значение, которое остается в таблице. Подзапрос в примере раздела Ре-

insert into dupes values (2, DYNAMITE)

insert into dupes values (3, DYNAMITE)

insert into dupes values (4, SHE SELLS)

insert into dupes values (5, SEA SHELLS)

insert into dupes values (6, SEA SHELLS)

insert into dupes values (7, SEA SHELLS)

select * from dupes order by 1

ID NAME

1 5 4

Затем команда DELETE удаляет из таблицы все строки с ID, не возвращенными подзапросом (в данном случае это 3, 6 и 7). Если возникают трудности с пониманием того, что происходит, выполните сначала подзапрос и включите NAME в список SELECT:

select name, min(id) from dupes group by name

NAME MIN(ID)

DYNAMITE 2

NAPOLEON 1

SEA SHELLS 5

SHE SELLS 4

Подзапрос возвращает строки, которые будут сохранены. Предикат NOT IN в выражении DELETE обусловливает удаление всех остальных строк.

Удаление записей, на которые есть ссылки в другой таблице

Задача

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

create table dept accidents ( deptno integer,

accident name varchar(20) )

insert into dept accidents values (10,BROKEN FOOT) insert into dept accidents values (10,FLESH WOUND) insert into dept accidents values (20,FIRE)

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

select min(id) from dupes group by name

MIN(ID)