SQL Server 入門 SQL Server 入門

ホーム > SQL Server の制約 > ユーザー定義関数を使った CHECK 制約を作成する

ユーザー定義関数を使った 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 テーブルのレコードは、それぞれ次のようになっています。

ユーザー定義関数を使った CHECK 制約を作成する 1

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

このスクリプトを実行すると、以下のようにユーザー定義関数が生成されました。

ユーザー定義関数を使った CHECK 制約を作成する 3


続いて、次のスクリプトを実行して、先ほど作った ufnTestResultCheck を使って、TestResultSummary テーブルに CHECK 制約を追加します。

ALTER TABLE TestResultSummary ADD CONSTRAINT CK_TestResultSummary_TestID
    CHECK (dbo.ufnTestResultCheck(TestID) = 1);

スクリプトを実行すると、以下のように CHECK 制約が生成されました。

ユーザー定義関数を使った CHECK 制約を作成する 4


試しに、以下のスクリプトで、TestResultSummary テーブルに TestID = 3 のデータを挿入してみます。

INSERT INTO TestResultSummary 
    (TestID, MinScore, MaxScore, AvgScore)
    VALUES
    (2, 0, 0, 0),
    (3, 0, 0, 0);

次のように、INSERT ステートメントが、先ほど追加した CHECK 制約の CK_TestResultSummary_TestID と競合しています、というエラーになって、データは挿入されませんでした。

ユーザー定義関数を使った CHECK 制約を作成する 5
ユーザー定義関数を使った CHECK 制約を作成する 6

TestID = 2 のデータだけにするとエラーにならず、インサートできました。

ユーザー定義関数を使った CHECK 制約を作成する 7


次は、UNIQUE 制約を作成してみましょう。

次へ SQL Server の UNIQUE 制約を作成する

SQL Server 関連の人気書籍
ホーム > SQL Server の制約 > ユーザー定義関数を使った CHECK 制約を作成する