都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

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

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

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

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

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

 私の Lenovo マシン上の SQL Server 2014 です。データベースはいつもの 『AdventureWorks2014』を使用しています。そろそろ、ディスクの容量が危険な感じになってきました。
f:id:koogucc11:20141223093540p:plain
 Lenovo yoga 3 欲しいなぁ。色はプラチナシルバー。SSD 512GB とか素敵すぎる。

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

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

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

 外部キー制約は、テーブル間の整合性を崩さずに格納可能なデータを制御する仕組みです。下図のダイアグラムに沿って説明します。
f:id:koogucc11:20141223110226p:plain
 上図で、Product テーブルからデータが削除されてしまい、その削除された Product テーブルのデータが WorkOrder テーブルで使用されていた場合、WorkOrder テーブル上に Product テーブルのレコードを参照できない、不正なレコードが存在することになります。外部キー制約は、このような参照整合性が崩れることを防ぎます。

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

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

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

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

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

 下図の外部キーを削除してみます。
f:id:koogucc11:20141223110226p:plain
 下記の DDL 文を SQL Server Management Studio で実行してみましょう。

ALTER TABLE Production.WorkOrder 
DROP CONSTRAINT FK_WorkOrder_Product_ProductID

f:id:koogucc11:20141223112309p:plain
 外部キー制約が外れています。
f:id:koogucc11:20141223112446p:plain
 下図の外部キーを元に戻してみます。下記の DDL 文を SQL Server Management Studio で実行してみましょう。

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

f:id:koogucc11:20141223112654p:plain
 外部キー制約が作成されました。
f:id:koogucc11:20141223112815p:plain

応用してみる

 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

おわりに何か書いてみる

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