SQL-Ex blog

Недавно я должен был удалить несколько родительских записей из таблицы. В данном случае родительская таблица имела внешние ключи, которые сами по себе не являлись проблемой. Факт состоял в том, что их было более 30. В то время как SQL Server будет с удовольствием сообщать вам о нарушении ограничения внешнего ключа, если имеется дочерняя запись для удаляемой родительской, нахождение их всех может стать обременительным. Это еще более справедливо, когда вы имеете большое число внешних ключей.

К счастью, SQL Server может сообщить нам массу информации о внешних ключах, включая, как родительские, так и дочерние таблицы, а также используемые столбцы. Имея эту информацию, мы можем динамически создать оператор SELECT, который сообщит нам число дочерних записей, которые привязаны к родительскому ID.

Таблицы

Мы можем использовать следующие системные таблицы для генерации нашего оператора SELECT.

Sys.foreign_key_columns

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

Sys.columns

Если мы соединим предыдущую таблицу с sys.columns, то получим имена столбцов, которые затем используются в окончательном операторе SELECT. Мы используем имена столбцов для конструирования оператора JOIN, который необходим для соединения родительской таблицы с дочерней.

Sys.objects

По разным причинам многие приложения используют различные схемы. Мы должны это учитывать, и использование Sys.objects позволяет нам определить имя схемы как для родительской, так и дочерней таблиц. Имя схемы используется в динамическом запросе для гарантии того, что JOIN соединяет правильные таблицы.

Запрос

Теперь, когда мы знаем основные необходимые нам таблицы, мы можем построить оператор SELECT, который сообщит нам:

• Имя родительской таблицы


• Имя столбца, используемого в родительской таблице


• Имя дочерней таблицы


• Имя столбца, используемого в дочерней таблице


• Оператор SELECT

-- Родительская таблица

DECLARE @tableName VARCHAR(150) = 'dbo.Product'

SELECT

    OBJECT_NAME(fkc.referenced_object_id) AS 'Parent Table'

    , parentcolumns.name AS 'Parent Column'

    , OBJECT_NAME(fkc.parent_object_id) AS 'Child Table'

    , childcolumns.name AS 'Child Column'

    , ' SELECT COUNT(1) as ''RowCount'', ''' + OBJECT_NAME(fkc.parent_object_id)+ ''' as ''ChildTable'' from ' + quotename(schema_name(o1.schema_id)) + '.' + object_name(fkc.referenced_object_id) + ' x 

            INNER JOIN ' + quotename(schema_name(o2.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) + ' y ON x.' + parentcolumns.name + ' = y.'+ childcolumns.name + ' UNION'  

FROM sys.foreign_key_columns fkc

    INNER JOIN sys.columns childcolumns ON fkc.parent_object_id = childcolumns.object_id AND fkc.parent_column_id = childcolumns.column_id -- получить дочерние столбцы

    INNER JOIN sys.columns parentcolumns ON fkc.referenced_object_id = parentcolumns.object_id AND fkc.referenced_column_id = parentcolumns.column_id -- получить родительские столбцы

    INNER JOIN sys.objects o1 ON fkc.referenced_object_id = o1.object_id -- получить имя родительской схемы

    inner JOIN sys.objects o2 on fkc.parent_object_id = o2.object_id -- получить имя дочерней схемы

WHERE fkc.referenced_object_id = OBJECT_ID(@tableName)

Если вы хотели увидеть все таблицы, можете опустить предложение WHERE в вышеприведенном запросе. Давайте посмотрим на таблицу Product в AdventureWorks2014.



Оператор SELECT генерируется в конце с использованием UNION. Это позволит выполнить весь блок операторов и вернуть единственный набор данных. Вы должны удалить последний UNION, иначе запрос выдаст ошибку.

Результаты

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

Специальные таблицы

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

В данном примере я использую табличную переменную просто для упрощения демонстрации, но с таким же успехом это может быть физическая или временная таблица. Просто помните, что табличная переменная или временная таблица должна быть создана в каждом сеансе, в котором вы выполняете более крупные запросы на выборку.

-- Родительская таблица

DECLARE @tableName VARCHAR(150) = 'Production.Product'

DECLARE @IDs TABLE (ids int)

INSERT @IDs (ids)

    SELECT 316 UNION

    SELECT 317 UNION

    SELECT 318 UNION

    SELECT 319 UNION

    SELECT 320

SELECT

    OBJECT_NAME(fkc.referenced_object_id) AS 'Parent Table'

    , parentcolumns.name AS 'Parent Column'

    , OBJECT_NAME(fkc.parent_object_id) AS 'Child Table'

    , childcolumns.name AS 'Child Column'

    , ' SELECT COUNT(1) as ''RowCount'', ''' + OBJECT_NAME(fkc.parent_object_id)+ ''' as ''ChildTable'' from @ids x 

            INNER JOIN ' + quotename(schema_name(o2.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) + ' y ON x.ids = y.'+ childcolumns.name + ' UNION' 

FROM sys.foreign_key_columns fkc

    INNER JOIN sys.columns childcolumns ON fkc.parent_object_id = childcolumns.object_id AND fkc.parent_column_id = childcolumns.column_id -- получить дочерние столбцы

    INNER JOIN sys.columns parentcolumns ON fkc.referenced_object_id = parentcolumns.object_id AND fkc.referenced_column_id = parentcolumns.column_id -- получить родительские столбцы

    INNER JOIN sys.objects o1 ON fkc.referenced_object_id = o1.object_id -- получить имя родительской схемы

    inner JOIN sys.objects o2 on fkc.parent_object_id = o2.object_id -- получить имя дочерней схемы

WHERE fkc.referenced_object_id = OBJECT_ID(@tableName)

Получив в результате оператор SELECT из вышеприведенного запроса, удаляя последний UNION, и выполняя оператор, вы сможете увидеть, в каких дочерних таблицах имеются связанные строки. Затем мы можем обратиться к этим строкам перед удалением родительских строк.

Заключение

SQL Server содержит массу информации о структурах объектов базы данных. Нужно просто знать, где искать эту информацию, чтобы построить свое решение. В данном случае, скрипт помог мне найти и обработать большое число дочерних записей, чтобы я смог разобраться с удалением родительских записей.

Само собой разумеется, что выполняйте его на свой страх и риск, и никогда не запускайте слепо код, загруженный из Интернета.