サブクエリでチェック制約が働かないことがある
トリガと違って、チェック制約は変更が行われたときに必ず起動されます。SQL Serverでは、管理者が正社員かどうか検証するサブクエリをチェック制約に実行させることができません。
alter table employee add constraint manager_is_employee check(
manager_id is null or
(select count(*) from employee e where e.employee_id = manager_id
and e.status = 'Employee') = 1
)
Msg 1046, Level 15, State 1, Line 3
Subqueries are not allowed in this context. Only scalar expressions are
allowed.
しかし、よく知られた簡単な回避策があります。このサブクエリをユーザー定義関数でラップするという方法です。
create function check_manager_status(@manager_id int) returns tinyint as begin declare @ret tinyint set @ret = (select count(*) from employee e where e.employee_id = @manager_id and e.status = 'Employee') return @ret end go
このユーザー定義関数をチェック制約内で起動します。
alter table employee add constraint manager_is_employee check( manager_id is null or dbo.check_manager_status(manager_id) = 1 )
このチェック制約は問題なく生成されており、正常に動作しているように見えます。次の4行は正常に挿入されます。
delete from employee insert into employee(employee_id, first_name, last_name, manager_id, status) values(1, 'Jane', 'Wilson', null, 'Employee') insert into employee(employee_id, first_name, last_name, manager_id, status) values(2, 'Sue', 'Smith', 1, 'Contractor') insert into employee(employee_id, first_name, last_name, manager_id, status) values(3, 'Sam', 'Brown', 1, 'Employee') insert into employee(employee_id, first_name, last_name, manager_id, status) values(4, 'Jill', 'Larsen', 3, 'Employee')
しかし、契約社員を報告先とする人物を挿入しようとすると失敗します。
insert into employee(employee_id, first_name, last_name, manager_id, status) values(5, 'Jack', 'Hansen', 2, 'Contractor') Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "manager_is_employee". The conflict occurred in database "test1",table "dbo.employee", column 'manager_id'. The statement has been terminated.
table "dbo.employee", column 'manager_id'.
The statement has been terminated.
ただし、このありふれたソリューションには抜け穴があります。Sam Brownが正社員ではなくなり、契約社員になったと仮定します。
update employee set status = 'Contractor' where employee_id = 3
この更新は正常に実行され、契約社員を報告先とする人物（Jill Larsen）がデータベース内に生じます。つまり、知らぬ間にビジネスルールは破られてしまいます。ここで、ひとまずデータの完全性を回復しましょう。
update employee set status = 'Employee' where employee_id = 3
この問題を何とか解決するために、別のチェック制約を作成します。これは最初の制約によく似ています。
create function number_of_employees(@manager_id int) returns int as begin declare @ret int set @ret = (select count(*) from employee e where e.manager_id = @manager_id and e.status = 'Employee') return @ret end go alter table employee add constraint contractor_has_no_employees check( status = 'Employee' or dbo.number_of_employees(employee_id) = 0 )
ここで、Sam Brownの状態を再び契約社員に変更してみます。見たところ、新しい制約によってデータの完全性は守られているようです。
update employee set status = 'Contractor' where employee_id = 3 Msg 547, Level 16, State 0, Line 1 The UPDATE statement conflicted with the CHECK constraint "contractor_has_no_employees". The conflict occurred in
SQL Server 2005が事態を複雑にする
これですべてに対応できたかと言えば、必ずしもそうでありません。SQL Server 2005がまだ問題です。この理由を説明するために、スナップショット分離を利用して、無効なデータをテーブルに入力することにします。Management Studioの1つのタブで、次のスナップショット分離レベルを用いてトランザクションを開始します。
SET TRANSACTION ISOLATION LEVEL SNAPSHOT begin transaction insert into employee(employee_id, first_name, last_name, manager_id, status) values(5, 'Jack', 'Hansen', 4, 'Contractor')
このトランザクションをまだコミットしていないことに注意してください。
Management Studioの別のタブ（つまり、別の接続）で、同じスナップショット分離レベルを使用することにします。このスナップショット分離レベルのおかげで、最初の接続の未コミット変更は見えません。そのため、それらの未コミット変更が現在の接続からの読み込みをブロックすることはありません。
SET TRANSACTION ISOLATION LEVEL SNAPSHOT select * from employee where manager_id=4
この選択クエリは、最初の接続の未コミット挿入でブロックされないため、すぐに復帰します。また、このクエリからは最初の接続の未コミット変更は見えないため、このクエリは何も返しません。従って、次の挿入ステートメントは成功します。
update employee set status = 'Contractor' where employee_id = 4
ここで両方のトランザクションをコミットすると、無効なデータがデータベースに取り込まれます。
select * from employee employee_id first_name last_name manager_id status ----------- ---------- ---------- ----------- ---------- 1 Jane Wilson NULL Employee 2 Sue Smith 1 Contractor 3 Sam Brown 1 Employee 4 Jill Setton 3 Contractor 5 Jack Hansen 4 Contractor (5 row(s) affected)
つまり、チェック制約内のサブクエリは、スナップショット分離を使用したときには機能しません。この点は、セキュリティ面で誤った印象をもたらしています。同じ理由から、トリガ内またはストアドプロシージャ内でこれらのサブクエリを使用してもスナップショット分離使用時には機能しません。
スナップショット分離に落とし穴があることがわかったので、正常に動作しない制約は削除しておきましょう。
alter table employee drop constraint manager_is_employee alter table employee drop constraint contractor_has_no_employees
そして、最後に後片付けをします（データの完全性を回復します）。
update employee set status = 'Employee' where employee_id = 4 delete from employee where employee_id=5
参照整合性を使うのが正しいやり方
このビジネスルールを実装する正しい方法を紹介しましょう。ここで使う参照整合性のために、さらに列（manager_status）を追加して設定します。
alter table employee add manager_status Varchar(10) check(manager_status in('Employee')) update employee set manager_status = 'Employee' where manager_id is not null
また、この新しい列に唯一の非Null値（Employee）か、Nullを格納できるようにします。
alter table employee add constraint manager_status_populated check( manager_id is null or manager_status is not null)
次に、参照整合性制約でmanager_statusの値が管理者の状態と一致するかを検証します。
alter table employee add constraint FKTarget unique(employee_id, status) alter table employee add constraint manager_is_employee foreign key(manager_id, manager_status) references employee(employee_id, Status)
これは先に述べたすべてのシナリオで正常に動作します。ただし、managerの状態をContractor（契約社員）に更新すると、多少分かりにくいエラーメッセージが返されます。
Msg 3960, Level 16, State 2, Line 1 Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.employee' directly or indirectly in database 'test1' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
この参照整合性制約は、どのような状況でも正常に動作し、データを常に保護します。それにしても、完璧な整合性を得るためのコストは相当なものです。実際、1つの列と、インデックスを追加する必要があるわけです。
知ることが賢い判断につながる
ビジネスが違えば、それに応じてニーズもさまざまに変化します。場合によっては、抜け穴があるとわかっているソリューションでも、それを使用しなければならないこともあるでしょう。その場合は、ここで紹介したような、ごくありふれた問題を承知しておくことが何よりも大切です。
