SQL Server で参照整合性制約 (Foreign Key 制約) が設定され、明示的にテーブル間のリレーションが設定されているかの情報を SSMS だとデータベースダイアグラムを作成することで確認することができます。
# 参照整合性制約が設定されているとテーブル間の JOIN をした際の効率性が (NULL の判断の最適化) 変わるはずなので、リレーション設定はいろいろなドキュメントで推奨されているかと。
似たような情報を SQL で取得するためのクエリを書いてみました。
取得するためのクエリが以下になります。
|
WITH foreign_key_table
AS (
SELECT
so.name table_name
, so.object_id table_object_id
, fk.key_index_id
, OBJECT_NAME(fk.object_id) parent_key_name
, fk.object_id parent_object_id
, sc1.name parent_column_name
, fkc.parent_column_id
, OBJECT_NAME(fkc.referenced_object_id) referenced_object_name
, fkc.referenced_object_id
, sc2.name referenced_column_name
, referenced_column_id
FROM
sys.foreign_keys fk
LEFT JOIN
sys.foreign_key_columns fkc
ON
fk.object_id = fkc.constraint_object_id
LEFT JOIN
sys.objects so
ON
fkc.parent_object_id = so.object_id
LEFT JOIN
sys.columns sc1
ON
fkc.parent_object_id = sc1.object_id
AND
fkc.parent_column_id = sc1.column_id
LEFT JOIN
sys.columns sc2
ON
fkc.referenced_object_id = sc2.object_id
AND
fkc.referenced_column_id = sc2.column_id
)
SELECT
1,
so.name,
fk.parent_key_name,
referenced_object_name
FROM
sys.objects so
LEFT JOIN
foreign_key_table fk
ON
so.object_id = fk.table_object_id
WHERE
so.type = ‘U’
AND
fk.table_name IS NULL
UNION
SELECT DISTINCT
2,
so.name,
fk.parent_key_name,
referenced_object_name
FROM
sys.objects so
LEFT JOIN
foreign_key_table fk
ON
so.object_id = fk.table_object_id
WHERE
so.type = ‘U’
AND
fk.table_name IS NOT NULL
ORDER BY 1,2a
|
実行結果がこちら。
参照整合性制約が設定されていないものは先頭が 1 となっています。
2 になっているものは参照整合性制約が設定されているものになります。
参照整合性制約が設定されているとテーブルに親子関係ができますので、以下のように削除をする時もどのような設定がされているかを意識する必要が出てきます。
# Truncate / Drop / Delete でそれぞれことなるエラーになってくるかと。
この場合は [customer] を参照している [orders] を先に削除する必要が出てきます。
さらに [orders] は [lineitem] に参照されていますので、削除は以下の順で実行する必要があります。
- [lineitem] → [orders] → [customer]
このつながりは削除をしたいオブジェクトが referenced_object_name に含まれているかを調べれば確認ができるのですが、階層構造になっているクエリを書くところまではできませんでした…。
時間があれば作成したいと思いますが当面はこのクエリで逃げようかと。
