SQL Server: 検証されていない not trusted の CHECK 制約や FOREIGN KEY 制約を trusted にする方法

検証されていない (not trusted) とはどういう状態？

CHECK 制約 と FOREIGN KEY 制約のオブジェクトカタログビューには、制約をシステムが検証したかどうかを示す is_not_trusted というフラグがあります。

この is_not_trusted が 1 の時には、実際に制約違反の値があるかどうかに関わらず、制約はシステムに検証されていないというステータスになっています。

is_not_trusted が 1 だと、SQL Server が実行プランを生成する際に、せっかくの制約が活用されません。

今回は検証されていない状態の CHECK 制約 と FOREIGN KEY 制約を、検証された状態 (is_not_trusted = 0) に戻す方法をご説明します。

検証されていない CHECK 制約 と FOREIGN KEY 制約を探す

検証されてない (is_not_trusted = 1) の CHECK 制約 と FOREIGN KEY 制約を探すクエリーは以下の通りです。

SELECT	SCHEMA_NAME(C.schema_id) AS SchemaName,
	O.name AS TableName,
	C.name AS CheckConstraintName,
	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	C.is_not_trusted = 1
	AND C.is_disabled = 0
	AND C.is_not_for_replication = 0;

SELECT	SCHEMA_NAME(F.schema_id) AS SchemaName,
	O.name AS TableName,
	F.name AS ForeignKeyName,
	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	F.is_not_trusted = 1
	AND F.is_disabled = 0
	AND F.is_not_for_replication = 0;

無効化されている制約は対象から除いています。

is_not_for_replication = 1 の制約は is_not_trusted が 1 になり、検証された trusted 状態にできないので、こちらも対象から除いています。

検証された状態 is_not_trusted = 0 に変更するクエリー

先ほどのスクリプトで検証されていない状態になってしまっている制約を見つけたら、以下のクエリーを使って trusted の状態に変更します。

ALTER TABLE [テーブル名] WITH CHECK CHECK CONSTRAINT [制約名];

CHECK CONSTRAINT の前に WITH CHECK をつけることで、システムが既存の値を検証し、全ての値が制約に合っている場合は is_not_trusted が 0 に変更されます。

制約違反の値が存在する場合は上記のクエリーを実行すると以下のようなエラーになります。

ALTER TABLE ステートメントは CHECK 制約 "制約名" と競合しています。競合が発生したのは、データベース "データベース名"、テーブル "テーブル名", column 'カラム名' です。

値を制約に合ったものに変更して、再度実行してください。

is_not_trusted = 1 になる原因は？

is_not_trusted が 1 になってしまう原因でよくあるのが、一時的に制約を無効な状態にして、データを投入したりした後で、有効にする際に以下のスクリプトで有効化してしまうケースです。

ダメ ALTER TABLE [テーブル名] CHECK CONSTRAINT [制約名];

有効化はされますが、is_not_trusted が 1 になります。

新しい値にのみ制約を有効にしたいような特別な状況では仕方がないかもしれませんが、それ以外の状況では忘れずに WITH CHECK をつけて制約を有効化してください。

他には、制約のついたテーブルに BULK INSERT をする際に CHECK_CONSTRAINTS のオプションを指定せずにデータをインサートした時にも is_not_trusted が 1 になりますのでご注意ください。