Как генерировать Операторы удаления в МН / SQL, на основе отношений таблиц FK?

Это отличное упражнение для развития ваших навыков PL / SQL и общих знаний Oracle!

Вам необходимо определить все ограниченные столбцы во всех таблицах с отношениями, производными от вашей главной таблицы. Вы можете получить всю необходимую информацию из двух представлений: ALL_CONSTRAINTS и ALL_CONS_COLUMNS . (Если все таблицы находятся в той же схеме, что и пользователь, выполняющий сценарий, вы можете использовать USER_CONSTRAINTS и USER_CONS_COLUMNS, если хотите)

Этот запрос найдет все ограничения внешнего ключа, которые ссылаются на данную таблицу ( CUSTOMER в этом примере):

SELECT constraint_name, table_name, constraint_type
  FROM all_constraints
 WHERE constraint_type = 'R'
   AND r_constraint_name IN (SELECT constraint_name
                               FROM all_constraints
                              WHERE constraint_type IN ('P', 'U')
                                AND table_name = 'CUSTOMER');


CONSTRAINT_NAME                C
------------------------------ -
CUSTOMER_FK1                   R
CUSTOMER_FK4                   R
CUSTOMER_FK5                   R
CUSTOMER_FK3                   R
CUSTOMER_FK2                   R

Теперь для каждого из результатов этого запроса вы можете использовать столбец CONSTRAINT_NAME , чтобы получить имя таблицы и столбца, которые вы можете использовать для написания операторов DELETE чтобы удалить все дочерние строки во всех дочерних таблицах.

Этот пример получает имя таблицы и столбца для ограничения с именем CUSTOMER_FK1

SELECT table_name, column_name
  FROM user_cons_columns
 WHERE constraint_name = 'CUSTOMER_FK1'

TABLE_NAME                    COLUMN_NAME                       
----------------------------- ------------------------------------
RESERVATION                   CUSTOMER_UID

Вы могли бы сделать, например:

DELETE FROM reservation
 WHERE customer_uid = 00153464

или

DELETE FROM reservation
 WHERE customer_uid IN (SELECT customer_uid
                          FROM customer
                         WHERE customer_type = 'X')

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

DELETE FROM reservation_detail 
 WHERE reservation_uid in (SELECT reservation_uid     
                             FROM reservation 
                            WHERE customer_uid IN (SELECT customer_uid
                                                     FROM customer
                                                    WHERE customer_type = 'X')

И если у reserve_detail также есть дочерние элементы ... вы поняли. Конечно, вы можете использовать объединения вместо вложенных запросов, но принцип тот же: чем глубже идут ваши зависимости, тем сложнее становятся ваши команды удаления.

Итак, теперь вы знаете, как это сделать, задача состоит в том, чтобы написать общий сценарий PL / SQL для удаления всех дочерних строк, строк внуков, строк правнуков ... (до бесконечности) для любой данной таблицы из вверх дном. Вам нужно будет использовать рекурсию . Должна быть интересная программа для написания!

(Последнее изменение: сценарий был удален; окончательное решение см. В другом моем ответе.)

Проблема в том, что ключевой столбец верхнего уровня не распространяется до самого низа. Если вы можете выполнить УДАЛИТЬ ИЗ grandchild WHERE parent_id =: 1, это нормально. Если вам нужно сделать,

DELETE FROM grandchild
WHERE child_id in (SELECT id FROM child WHERE parent_id = :1)

, то при спуске на шесть или семь пунктов вы получите уродливые (и, вероятно, медленные) запросы.

Хотя вы сказали, что не можете сделать ограничения КАСКАДНЫМ, можете ли вы сделать их откладываемыми изначально немедленно? Это не повлияет на существующий код. Ваш сеанс удаления отложит все ограничения. Затем удалите из родительского элемента, удалите из дочернего элемента, где записи не было в родительском элементе, удалите из внука, где нет совпадения в дочернем элементе и т. Д.