SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server の参照整合性制約の状態を取得

leave a comment

SQL Server で参照整合性制約 (Foreign Key 制約) が設定され、明示的にテーブル間のリレーションが設定されているかの情報を SSMS だとデータベースダイアグラムを作成することで確認することができます。
# 参照整合性制約が設定されているとテーブル間の JOIN をした際の効率性が (NULL の判断の最適化) 変わるはずなので、リレーション設定はいろいろなドキュメントで推奨されているかと。

image

似たような情報を 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

実行結果がこちら。
image

参照整合性制約が設定されていないものは先頭が 1 となっています。
2 になっているものは参照整合性制約が設定されているものになります。

参照整合性制約が設定されているとテーブルに親子関係ができますので、以下のように削除をする時もどのような設定がされているかを意識する必要が出てきます。
# Truncate / Drop / Delete でそれぞれことなるエラーになってくるかと。
image

この場合は [customer] を参照している [orders] を先に削除する必要が出てきます。
さらに [orders] は [lineitem] に参照されていますので、削除は以下の順で実行する必要があります。

  • [lineitem] → [orders] → [customer]

このつながりは削除をしたいオブジェクトが referenced_object_name に含まれているかを調べれば確認ができるのですが、階層構造になっているクエリを書くところまではできませんでした…。

時間があれば作成したいと思いますが当面はこのクエリで逃げようかと。

Written by masayuki.ozawa

5月 24th, 2013 at 12:23 am

Posted in SQL Server

Tagged with

Leave a Reply

*