ユーザー定義関数を使った CHECK 制約を作成する
ユーザー定義関数を使った CHECK 制約
前回 「SQL Server の CHECK 制約を作成する」 では、既存のテーブルにシンプルな CHECK 制約を追加して動作を確認しました。
今回は、ユーザー定義関数を使った CHECK 制約を作成してみましょう。
こちら のスクリプトを実行して、テーブルを再生成しておいてください。 全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。
ユーザー定義関数を使った CHECK 制約を作成する
既存のテーブルに、ユーザー定義関数を使った CHECK 制約を追加する構文は、前回と同じで、[条件文] にユーザ定義を使って True/False を判断させるだけです。
ALTER TABLE [テーブル名] ADD CONSTRAINT [チェック制約名] CHECK ([条件文]);
構文に関しては 「SQL Server の CHECK 制約を作成する」 をご覧ください。
CHECK 制約は [条件文] を評価した結果が False の時にエラーを出すという仕組みなので、[条件文] を評価した結果が NULL になった時はエラーになりません。
ユーザー定義関数を使った時は特にわかりにくいので NULL にならないようになっているか、もしくは NULL のケースに値が入ってしまってもいいのか、お気をつけくださいね。
それでは、ユーザー定義関数を CHECK 制約を追加してみましょう。
現在、Test、TestResult、TestResultSummary テーブルのレコードは、それぞれ次のようになっています。
TestResultSummary テーブルに、TestResult テーブルに TestID が存在していない場合は、その TestID を保存できないようにする CHECK 制約を作ってみましょう。
まず、以下のスクリプトを実行して、TestID を入力パラメーターとして受け取って、TestResult テーブルに存在している場合は 1、存在していなければ 0 の BIT を返す、ユーザー定義関数を作ります。
CREATE FUNCTION ufnTestResultCheck (
@TestID INT
)
RETURNS BIT
AS
BEGIN
RETURN CASE
WHEN EXISTS
(SELECT *
FROM TestResult
WHERE TestID = @TestID)
THEN 1
ELSE 0
END;
END
このスクリプトを実行すると、以下のようにユーザー定義関数が生成されました。
続いて、次のスクリプトを実行して、先ほど作った ufnTestResultCheck を使って、TestResultSummary テーブルに CHECK 制約を追加します。
ALTER TABLE TestResultSummary ADD CONSTRAINT CK_TestResultSummary_TestID CHECK (dbo.ufnTestResultCheck(TestID) = 1);
スクリプトを実行すると、以下のように CHECK 制約が生成されました。
試しに、以下のスクリプトで、TestResultSummary テーブルに TestID = 3 のデータを挿入してみます。
INSERT INTO TestResultSummary (TestID, MinScore, MaxScore, AvgScore) VALUES (2, 0, 0, 0), (3, 0, 0, 0);
次のように、INSERT ステートメントが、先ほど追加した CHECK 制約の CK_TestResultSummary_TestID と競合しています、というエラーになって、データは挿入されませんでした。
TestID = 2 のデータだけにするとエラーにならず、インサートできました。
次は、UNIQUE 制約を作成してみましょう。