TRUNCATE TABLE の時は制約削除しなきゃねと思って久しぶりに SQL Server 関連の記事書いてみた

今回やることを説明してみる

　少し前に制約に関する記事を書きました。

制約を無効にしたり、有効にしたりしてみる - 都内で働くSEの技術的なひとりごと
　
　データのINSERT,UPDATE,DELETE 時にはいいのですが、データを初期化をするような TRUNCATE TABLE などを実行する場合は、外部キー制約は無効化ではなく、消去する必要があります。

使用する環境を説明してみる

　私の Lenovo マシン上の SQL Server 2014 です。データベースはいつもの 『AdventureWorks2014』を使用しています。そろそろ、ディスクの容量が危険な感じになってきました。

　Lenovo yoga 3 欲しいなぁ。色はプラチナシルバー。SSD 512GB とか素敵すぎる。

Lenovo YOGA 3 Pro(Windows 8.1 64bit/Core M-5Y70/8GB/512GB SSD/13.3型 QHD+IPS液晶/Office H&B Premium プラス Office 365 サービス/プラチナシルバー)80HE00CPJP

本題に入る前に少し制約などについて説明してみる

　いきなり外部キー制約を外す DDL 文とかクエリとか書いてしまってもいいのですが、外部キー制約とはなにか、テーブル間のリレーションを SQL Server Management Studio で見やすく表現するデータベースダイヤグラムについて簡単に説明します。

外部キー制約について説明してみる

　外部キー制約は、テーブル間の整合性を崩さずに格納可能なデータを制御する仕組みです。下図のダイアグラムに沿って説明します。

　上図で、Product テーブルからデータが削除されてしまい、その削除された Product テーブルのデータが WorkOrder テーブルで使用されていた場合、WorkOrder テーブル上に Product テーブルのレコードを参照できない、不正なレコードが存在することになります。外部キー制約は、このような参照整合性が崩れることを防ぎます。

データベースダイヤグラムについて説明してみる

　外部キー制約を可視化するツールです。外部キー制約の説明でも使用しました。早速、データベースダイアグラムを作成しましょう。

• SQL Server Management Studio のオブジェクトエクスプローラーのツリーからデータベースダイアグラムを右クリックし、新しいデータベースダイアグラムをクリックします。
• WorkOrder を選択し、追加をクリックします。
• WorkOrder テーブルがデザイナ上に追加されます。
• WorkOrder のヘッダ部分を右クリックし、関連テーブルの追加をクリックします。
• 関連するテーブルがデザイナ上に追加されます。
• 見やすくするため、テーブルを移動します。

　これで関連が見やすくなりました。是非使ってみてください。

本題の外部キー制約を削除しちゃうクエリを書いてみる

　下図の外部キーを削除してみます。

　下記の DDL 文を SQL Server Management Studio で実行してみましょう。

ALTER TABLE Production.WorkOrder 
DROP CONSTRAINT FK_WorkOrder_Product_ProductID

　外部キー制約が外れています。

　下図の外部キーを元に戻してみます。下記の DDL 文を SQL Server Management Studio で実行してみましょう。

ALTER TABLE Production.WorkOrder WITH CHECK 
ADD CONSTRAINT FK_WorkOrder_Product_ProductID FOREIGN KEY(ProductID)
REFERENCES Production.Product (ProductID)

　外部キー制約が作成されました。

応用してみる

　ALTER 文を個々に記述していたのでは、面倒でかつテーブル追加時も毎回変更しなければいけないので、WorkOrder の制約を一括で削除、再作成ができるようなクエリを書いてみましょう。(ちょっと手抜きですが....)

USE AdventureWorks2014
SELECT [制約名] = o.name,
       [スキーマ名] = s1.name,
       [テーブル名] = t1.name,
       [カラム名] = c1.name,
       [参照スキーマ] = s2.name,
       [参照テーブル] = t2.name,
       [参照カラム] = c2.name
INTO #CONSTRAINTS
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects o
    ON o.object_id = fkc.constraint_object_id
INNER JOIN sys.tables t1
    ON t1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas s1
    ON t1.schema_id = s1.schema_id
INNER JOIN sys.columns c1
    ON c1.column_id = parent_column_id 
        AND c1.object_id = t1.object_id
INNER JOIN sys.tables t2
    ON t2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns c2
    ON c2.column_id = referenced_column_id 
        AND c2.object_id = t2.object_id
INNER JOIN sys.schemas s2
    ON t2.schema_id = s2.schema_id
        AND c2.column_id = referenced_column_id 
            AND c2.object_id = t2.object_id
WHERE t1.name = 'WorkOrder'

DECLARE @ConstraintName nvarchar(200)
DECLARE @SchemaName nvarchar(200)
DECLARE @TableName nvarchar(200)
DECLARE @ColumnName nvarchar(200)
DECLARE @ReferenceSchemaName nvarchar(200)
DECLARE @ReferenceTableName nvarchar(200)
DECLARE @ReferenceColumnName nvarchar(200)

DECLARE DROPCONSTRAINT CURSOR FOR SELECT * FROM #CONSTRAINTS
OPEN DROPCONSTRAINT

FETCH NEXT FROM DROPCONSTRAINT INTO @ConstraintName,
                                    @SchemaName,
                                    @TableName,
				    @ColumnName,
				    @ReferenceSchemaName,
				    @ReferenceTableName,
				    @ReferenceColumnName
WHILE @@FETCH_STATUS = 0
    BEGIN
        EXECUTE('ALTER TABLE ' + @SchemaName + '.' + @TableName + ' ' + 
	        'DROP CONSTRAINT ' + @ConstraintName)
　　    FETCH NEXT FROM DROPCONSTRAINT INTO @ConstraintName,
                                            @SchemaName,
	      	                            @TableName,
                                            @ColumnName,
                                            @ReferenceSchemaName,
                                            @ReferenceTableName,
                                            @ReferenceColumnName
    END
CLOSE DROPCONSTRAINT
DEALLOCATE DROPCONSTRAINT

--TRUNCATEなど色々と処理を記述

--TRUNCATEなど色々と処理を記述

DECLARE ADDCONSTRAINT CURSOR FOR SELECT * FROM #CONSTRAINTS
OPEN ADDCONSTRAINT

FETCH NEXT FROM ADDCONSTRAINT INTO @ConstraintName,
                                   @SchemaName,
                                   @TableName,
                                   @ColumnName,
                                   @ReferenceSchemaName,
                                   @ReferenceTableName,
                                   @ReferenceColumnName
WHILE @@FETCH_STATUS = 0
    BEGIN
        EXECUTE('ALTER TABLE ' + @SchemaName + '.' + @TableName + ' ' +
                'ADD CONSTRAINT ' + @ConstraintName + ' FOREIGN KEY(' + @ColumnName + ')' + 
                'REFERENCES ' + @SchemaName + '.' + @ReferenceTableName + '(' + @ReferenceColumnName + ') ')
　　    FETCH NEXT FROM ADDCONSTRAINT INTO @ConstraintName,
                                           @SchemaName,
                                           @TableName,
                                           @ColumnName,
                                           @ReferenceSchemaName,
                                           @ReferenceTableName,
                                           @ReferenceColumnName
      
    END
CLOSE ADDCONSTRAINT
DEALLOCATE ADDCONSTRAINT

DROP TABLE #CONSTRAINTS

おわりに何か書いてみる

　エラー処理とかまったくやっていないので、実際使うのはまずそうですが、参考までにどうぞ♪時間があったら、エラー処理を含め、きちんと書きたいと思います。※しかし、最近ブログをきちんと書く時間ないな...