INSERT ステートメントと UPDATE ステートメントで外部キー制約を無効にする

  • [アーティクル]

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

SQL Server では、SQL Server Management Studio または Transact-SQL を使って、INSERT および UPDATE トランザクションの間に外部キー制約を無効にできます。 新しいデータが既存の制約に違反しないことがわかっている場合、またはデータベース内の既存のデータのみに制約を適用する場合に、このオプションを使用します。

制限事項と制約事項

これらの制約を無効にすると、今後列に行われる挿入または更新は、制約条件に対して検証されません。

アクセス許可

テーブルに対する ALTER 権限が必要です。

SQL Server Management Studio を使用します。

INSERT ステートメントまたは UPDATE ステートメントに対して外部キー制約を無効にするには

  1. オブジェクト エクスプローラーで、制約が含まれているテーブルを展開し、 [キー] フォルダーを展開します。

  2. 制約を右クリックし、 [変更]をクリックします。

  3. [テーブル デザイナー] の下のグリッドで、[外部キーの制約を適用] を選択し、ドロップダウン ボックスの一覧の [いいえ] を選択します。

  4. [閉じる] を選択します。

  5. 必要に応じて制約を再び有効にするには、上記の手順の逆を行います。 [外部キーの制約を適用] を選択して、ドロップダウン ボックスの一覧で [はい] を選択します。

  6. 外部キーのリレーションシップで既存のデータをチェックすることにより制約を信頼するには、[作成または再度有効化するときに既存データを確認] を選んで、ドロップダウン メニューから [はい] を選びます。 これにより、外部キー制約が信頼されるようになります。

  • [作成または再度有効化するときに既存データを確認] が [いいえ] に設定されていると、外部キーは、もう一度有効にされても既存のデータをチェックしません。 そのため、クエリ オプティマイザーはパフォーマンスの向上の可能性を考慮できません。 信頼された外部キーは、外部キー制約に基づく想定により実行プランを簡略化するために使用できるため、推奨されます。 データベースで外部キーが信頼されているかどうかを調べるには、この記事で後述するサンプル クエリを参照してください。

Transact-SQL の使用

INSERT ステートメントまたは UPDATE ステートメントに対して外部キー制約を無効にするには

  1. オブジェクト エクスプローラーで、 データベース エンジンのインスタンスに接続します。

  2. 標準バーで、 [新しいクエリ] を選択します。

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] を選択します。

    USE AdventureWorks2022;  
GO  
ALTER TABLE Purchasing.PurchaseOrderHeader  
NOCHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;  
GO

  4. 必要に応じて制約を再び有効にするには、次の例をコピーしてクエリ ウィンドウに貼り付け、[実行] を選択します。

    USE AdventureWorks2022;  
GO  
ALTER TABLE Purchasing.PurchaseOrderHeader  
CHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;  
GO

  5. 環境において制約が信頼され、有効になっていることを確認します。 is_not_trusted が 1 の場合、外部キーが再有効化または再作成されたときに、既存データのチェックは行われません。 そのため、クエリ オプティマイザーはパフォーマンスの向上の可能性を考慮できません。 信頼された外部キーは、外部キー制約に基づく想定により実行プランを簡略化するために使用できるため、推奨されます。 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] を選択します。

    SELECT o.name, fk.name, fk.is_not_trusted, fk.is_disabled
FROM sys.foreign_keys AS fk
INNER JOIN sys.objects AS o ON fk.parent_object_id = o.object_id
WHERE fk.name = 'FK_PurchaseOrderHeader_Employee_EmployeeID';
GO

    テーブル内の既存のデータが外部キー制約に準拠している場合は、外部キー制約を信頼済みに設定する必要があります。 外部キーを信頼済みに設定するには、次のスクリプトを使って外部キー制約をもう一度信頼します。追加されている WITH CHECK 構文に注意してください。 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] を選択します。

    ALTER TABLE [Purchasing].[PurchaseOrderHeader] 
WITH CHECK 
CHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;
GO

次のステップ