外部キー (FOREIGN KEY) と CHECK 制約を無効化(disable) / 有効化(enable) する方法

外部キー (FOREIGN KEY) と CHECK 制約を無効化(disable) / 有効化(enable) するには？

SQL でテーブルにデータを投入したりする際に、一時的に外部キーや CHECK 制約を無効にして、データの投入後、有効にもどしたいような時ありませんか？

ここでは、そんな時に使える、外部キー (FOREIGN KEY) と CHECK 制約を無効化・有効化するスクリプトをご紹介します。

あるテーブルの特定の外部キー (FOREIGN KEY) や CHECK 制約を無効化・有効化する方法

まずはあるテーブルの、特定の外部キー (FOREIGN KEY) や CHECK 制約を無効化・有効化するスクリプトです。

--無効化するスクリプト
ALTER TABLE [テーブル名] NOCHECK CONSTRAINT [外部キー・CHECK制約名];

--有効化するスクリプト
ALTER TABLE [テーブル名] WITH CHECK CHECK CONSTRAINT [外部キー・CHECK制約名];

例えば、次のような外部キーとチェック制約のついた TestResult というテーブルがあります。

「FK__TestResul__Stude__4E88ABD4」という名前の外部キー制約を無効化したい時は次のようにできます。

-- 外部キー制約を無効化
ALTER TABLE TestResult NOCHECK CONSTRAINT FK__TestResul__Stude__4E88ABD4;

-- ステータス確認のためのスクリプト
SELECT  F.name,
        F.is_disabled, 
        F.is_not_trusted
FROM    sys.foreign_keys AS F
           INNER JOIN sys.objects AS O
              ON F.parent_object_id = O.object_id
WHERE   O.name = 'TestResult'
        AND F.name = 'FK__TestResul__Stude__4E88ABD4';

「FK__TestResul__Stude__4E88ABD4」外部キー制約の is_disabled カラムの値が 1 になって無効化されているのがわかります。

次のスクリプトで外部キー制約を有効化することができます。

-- 外部キー制約を有効化
ALTER TABLE TestResult WITH CHECK CHECK CONSTRAINT FK__TestResul__Stude__4E88ABD4;

-- ステータス確認のためのスクリプト
SELECT  F.name,
        F.is_disabled, 
        F.is_not_trusted
FROM    sys.foreign_keys AS F
           INNER JOIN sys.objects AS O
              ON F.parent_object_id = O.object_id
WHERE   O.name = 'TestResult'
        AND F.name = 'FK__TestResul__Stude__4E88ABD4';

「FK__TestResul__Stude__4E88ABD4」外部キー制約の is_disabled カラムの値が 0 になって有効化されているのがわかります。

CHECK 制約も全く同じ方法で無効化・有効化できます。

あるテーブルの全ての外部キー (FOREIGN KEY) と CHECK 制約を無効化・有効化する方法

次は、あるテーブルの全ての外部キー (FOREIGN KEY) と CHECK 制約を無効化・有効化する方法です。

先ほど、[外部キー・CHECK制約名] を指定していた箇所を ALL に変更すると、指定したテーブルの全ての 外部キー (FOREIGN KEY) と CHECK 制約が無効化・有効化できます。

--無効化するスクリプト
ALTER TABLE [テーブル名] NOCHECK CONSTRAINT ALL;

--有効化するスクリプト
ALTER TABLE [テーブル名] WITH CHECK CHECK CONSTRAINT ALL;

先ほどの TestResult テーブルの全ての外部キーと CHECK 制約を無効化したい時は次のようにできます。

-- 制約を無効化
ALTER TABLE TestResult NOCHECK CONSTRAINT ALL;

-- ステータス確認のためのスクリプト
SELECT  F.name,
        F.is_disabled, 
        F.is_not_trusted
FROM    sys.foreign_keys AS F
           INNER JOIN sys.objects AS O
              ON F.parent_object_id = O.object_id
WHERE   O.name = 'TestResult';

SELECT  C.name,
        C.is_disabled, 
        C.is_not_trusted
FROM    sys.check_constraints AS C
            INNER JOIN sys.objects AS O
              ON C.parent_object_id = O.object_id
WHERE   O.name = 'TestResult';

有効化に戻すには次のようにできます。

-- 制約を有効化
ALTER TABLE TestResult WITH CHECK CHECK CONSTRAINT ALL;

-- ステータス確認のためのスクリプト
SELECT  F.name,
        F.is_disabled, 
        F.is_not_trusted
FROM    sys.foreign_keys AS F
           INNER JOIN sys.objects AS O
              ON F.parent_object_id = O.object_id
WHERE   O.name = 'TestResult';

SELECT  C.name,
        C.is_disabled, 
        C.is_not_trusted
FROM    sys.check_constraints AS C
            INNER JOIN sys.objects AS O
              ON C.parent_object_id = O.object_id
WHERE   O.name = 'TestResult';

データベースの全ての外部キー (FOREIGN KEY) と CHECK 制約を無効化・有効化する方法

最後に、データベースの全ての外部キー (FOREIGN KEY) と CHECK 制約を無効化・有効化する方法です。

sp_MSForEachTable を使って、先ほどの、あるテーブルの全ての外部キー (FOREIGN KEY) と CHECK 制約を無効化・有効化するスクリプトを全テーブルに対して実行します。

sp_MSForEachTable はマイクロソフトによってドキュメントされていないストアドプロシージャで、引数で指定したコマンドを全てのユーザーテーブルについて実行してくれます。

--無効化するスクリプト
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL;'

--有効化するスクリプト
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL;'

データベースを選択して、次のスクリプトを実行するとデータベースの全ての外部キー (FOREIGN KEY) と CHECK 制約が無効化されます。

-- 制約を無効化
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL;'

-- ステータス確認のためのスクリプト
SELECT   O.name AS TableName,
         F.name,
         F.is_disabled, 
         F.is_not_trusted
FROM     sys.foreign_keys AS F
            INNER JOIN sys.objects AS O
               ON F.parent_object_id = O.object_id;

SELECT   O.name AS TableName,
         C.name,
         C.is_disabled, 
         C.is_not_trusted
FROM     sys.check_constraints AS C
            INNER JOIN sys.objects AS O
               ON C.parent_object_id = O.object_id;

Test 用のデータベースなので、制約の数が少ないですが、TestResult テーブルだけではなく、TestResultSummary というテーブルの制約も無効化されています。

次のスクリプトで、データベースの全ての外部キー (FOREIGN KEY) と CHECK 制約が有効化されます。

-- 制約を有効化
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL;'

-- ステータス確認のためのスクリプト
SELECT   O.name AS TableName,
         F.name,
         F.is_disabled, 
         F.is_not_trusted
FROM     sys.foreign_keys AS F
            INNER JOIN sys.objects AS O
               ON F.parent_object_id = O.object_id;

SELECT   O.name AS TableName,
         C.name,
         C.is_disabled, 
         C.is_not_trusted
FROM     sys.check_constraints AS C
            INNER JOIN sys.objects AS O
               ON C.parent_object_id = O.object_id;

ALL キーワードを使って外部キー (FOREIGN KEY) と CHECK 制約を有効化すると、そのテーブルの全ての外部キー (FOREIGN KEY) と CHECK 制約が有効化されます。

一時的に外部キーや CHECK 制約を無効にして、データの投入後、有効にもどしたいような状況の時は、意図的に無効化していた制約まで有効化してしまわないように気をつけてくださいね！

以上、外部キー (FOREIGN KEY) と CHECK 制約を、無効化 (disable)・有効化 (enable) するスクリプトをご紹介しました。