INSERT時の大小比較を含む重複チェック

質問

会議室予約システムを例とさせていただきます。

開始時刻／終了時刻／予約者
10:00／11:00／鈴木
15:00／17:00／山田

このテーブルに以下のデータを追加する時、【追加データ１】はINSERT可能で、【追加データ２】はINSERT不可能にするチェック方法を考えます。
【追加データ１】14:00／15:00／佐藤
【追加データ２】16:00／18:00／高橋

一般にはプログラム内でSELECTによるチェックを行い判定結果によりINSERTを行うと思いますが、今回は既存プログラムに手を加えることができないとして、以下のどれかのやり方の可能性と方法を教えていただきたいです。

【方法Ａ】check制約のようなDB依存しない方法で、テーブルにあらかじめチェックを加える。
【方法Ｂ】SQLServer特有の方法を使用し、テーブルにあらかじめチェックを加える。
【方法Ｃ】INSERTのSQLにチェック機能を入れる（入力不可時にはエラー等を返す）

プログラム言語：java 1.5 以上, iBATIS
SQLServer：2008 R2 Express または Standard

なるべく【方法Ａ】のようなものがあるといいのですが。。
以上です。よろしくお願いいたします。

回答No.5

nora1962 · ベストアンサー

すみません訂正です。

create table 会議室予約
(
id int identity primary key,
会議室ID varchar(20),
start_time datetime,
end_time datetime
)
go
create index idx_会議室 on 会議室予約 ( 会議室ID, start_time, end_time )
go

create function chk_会議室予約() returns int
begin
declare @i int
select @i = sum(t.CNT) from (
select count(*) CNT from 会議室予約 y where exists (
select 1 from 会議室予約 y2
where y.id <> y2.id
and y.会議室ID = y2.会議室ID
and y.start_time > y2.start_time and y.start_time < y2.end_time )
union all
select count(*) from 会議室予約 y where exists (
select 1 from 会議室予約 y2
where y.id <> y2.id
and y.会議室ID = y2.会議室ID
and y.end_time < y2.start_time and y.start_time < y2.end_time )
union all
select count(*) from 会議室予約 y where exists (
select 1 from 会議室予約 y2
where y.id <> y2.id
and y.会議室ID = y2.会議室ID
and y2.start_time > y.start_time and y2.start_time < y.end_time )
union all
select count(*) from 会議室予約 y where exists (
select 1 from 会議室予約 y2
where y.id <> y2.id
and y.会議室ID = y2.会議室ID
and y2.end_time > y.start_time and y2.start_time < y.end_time ) ) t
return @i
end;
go

制約の作成は変わりません。

お礼

１）'roomA','2011/1/1','2011/1/3'　【登録可】
２）'roomA','2011/1/2','2011/1/4'　【登録不可】
３）'roomA','2011/1/5','2011/1/6'　【登録不可】
４）'roomB','2011/1/5','2011/1/6'　【登録可】

上記データをテスト登録したところ、【】内の結果になりました。
２）がはじかれた時に「やった！」と思ったものの、３）が出来ず。
試しに４）をやったらできました。

そこで条件を以下の３つでやることにしました。
・y.start_time between y2.start_time and y2.end_time
・y.end_time between y2.start_time and y2.end_time
・y.start_time < y2.start_time and y2.end_time < y.end_time

UPDATE文でも上手く機能することを確認できました。

できましたが、、、レベルが高くわからないことが多いです。。。
図々しいお願いで申し訳ありませんが、簡単な解説願えませんでしょうか。

入力されたデータがfunctionに渡されていないのは、いったんテーブルに書き込んでから消しているイメージなのでしょうか？
登録不可だったINSERTの分もＩＤが増えているようなので、そうかなと思うものの、いかんせんDeleteに当たる部分がないもので、？？？です。。

それにしてもこんな複合技があるとは、、、nora1962さん凄いです。。
ベストアンサーは No4 の Siegruneさんの回答を待ってから選出しますが、とりあえずお礼までを。
本当にありがとうございました！！

補足

No.6 SiegruneさんのTriggerバージョンを見て気が付きました。
Triggerのやり方でも追加失敗すると連番が一つ飛びになっているのですが、これは rollback が効いていたからだったんですね。。

調べたら書いてありました。
>列または列の集合に対するCHECK制約では、その表のすべての行について、指定した条件がTRUEまたはUNKNOWNであることが必要。 DML文の結果でCHECK制約の条件がFALSEに評価される場合、その文はロールバックされる。

追加質問は取り消させていただきます。ありがとうございました！

回答No.7

Siegrune

書き忘れ。

ANO6のRAISERRORはSQL Server2005の新機能です。
（このQ/Aを参照された他のかたのために記載しようと思って忘れてました。
・・・といいつつ、書いている本人のためだったりして。）

お礼

ありがとうございます。Siegruneさん、nora1962さん両氏のおかげで
このエントリーは多くの人にとって、とても有用なものになると思います。

「nora1962さんのチェック制約による方法」「SiegruneさんのTriggerによる方法」
共に当初望んでいた結果が出せました。

ベストアンサーはお二人につけたいところなのですが、一つの回答しか選べないということで、
悩んだ結果、「nora1962さんのチェック制約による方法」を選択させていただきました。

これは、質問の「【方法Ａ】check制約のようなDB依存しない方法で・・・」を満たしているためです。
ちょっと調べてみたのですがOracleにもエラーメッセージを作成する方法があるものの、やはりTriggerによる方法はDB固有さがあり、汎用性では劣るようです。

とはいっても、「SiegruneさんのTriggerによる方法」がシンプルで理解しやすく、エラーメッセージも自由に作れるなど、SQLServerを使う限りはこちらの方法を利用した方が実装も含め使い勝手がいいと思います。（僕もこちらを採用するつもりです）

以上でこちらのエントリーは終了とさせて頂きます。ありがとうございました！！

回答No.6

Siegrune

ANo4です。

１）トリガーはINSERTする前の状態でチェックを行い、不可であればエラーを返すことができるのでしょうか？
２）またそのエラーはSQLエラーと同じでしょうか？（既存のプログラムに別途トリガー用のエラーを記述する必要があるでしょうか？もちろん作りによると思うのですが。。）

create trigger で、for Insert　とするとInsert前の状態でチェックします。
（after UpdateならUpdate後の状態でチェックするなど）
不可のときは、RAISERRORでエラーを返します。
これは、デフォルトで50000のエラーコードを返すエラーとなります。
（50000より大きい値も可能。但し、エラーメッセージは、sys.messages に入れておかないとダメだったかとおもいます。文字列を指定すると50000が返ると思います。）
既存のプログラムの処理しだいですが、
SQLエラーコード：xxxxx
エラーメッセージ：ＮＮＮＮＮＮ
と表示するだけなら変更しなくても大丈夫かと思います。

例）Table1へのInsert およびUpdate前にa1列に入れようとした値が'あいう'のときエラーにする
トリガーの処理です。InsertやUpdateされた処理後の情報は　
Insertedテーブルにコピーが格納されます。

CREATE TRIGGER Trigger1
ON dbo.Table1
for INSERT, UPDATE
AS
begin
declare @testStr nchar(10)
select @testStr = a1 from Inserted
if @testStr = 'あいう'
begin
RAISERROR ('NULL ERROR',16,1)
rollback transaction
end
end

INSERT INTO Table1 (a1, a2)
VALUES ('あいう', 'A')

結果は添付画像参照。
エラーメッセージに「NULL ERROR」と出ています。
## いい加減なメッセージですいません。

お礼

素晴らしい！まさにやりたいことでした！
SQLでエラーメッセージが作れるというのも大変使い勝手がいいですね！

本当にありがとうございました！！

回答No.4

Siegrune

>【方法Ｂ】SQLServer特有の方法を使用し、テーブルにあらかじめチェックを加える。
別にSQLServer特有の方法って訳でもないですが、
トリガー使えば？
と思います。（現在、使っていないなら。）

ちなみに【追加データ１】でなく
【更新データ１】10:00／11:00／鈴木　を　10:00／17:30／鈴木　にする
というのがあったらどうするのでしょう？
delete/insertするってことでも問題ないですし、
updateするにしても、トリガーが使えるなら、insert,updateで作れば問題ないですけど。
・・・考慮必要ですよという示唆です。

## 作り方については、現在使っているなら書く意味ないので、その答え待ちとします。
## また、自分で調べて作れるなら別に追加回答はいらないでしょうし。

補足

「トリガーはデータに変更があった時に何かをするもの」という認識しかなく、実装経験もないので、まったく素人の質問ですが、以下２点について教えていただけませんでしょうか？

１）トリガーはINSERTする前の状態でチェックを行い、不可であればエラーを返すことができるのでしょうか？
２）またそのエラーはSQLエラーと同じでしょうか？（既存のプログラムに別途トリガー用のエラーを記述する必要があるでしょうか？もちろん作りによると思うのですが。。）

不勉強で申し訳ありませんが、ご教示お願いいたします。

回答No.3

nora1962

> １件１件のデータ追加間隔は十分な時間があるとした場合、何か方法はありませんでしょうか？

そうですね。
create table 会議室予約
(
id int identity primary key,
会議室ID varachar(20),
start_time datetime,
end_time datetime
)
go
create index idx_会議室 on 会議室予約 ( 会議室ID )
go

create function chk_会議室予約() returns int
begin
declare @i int
select @i = count(*) from 会議室予約 y where exists (
select 1 from 会議室予約 y2
where y.id <> y2.id
and y.会議室ID = y2.会議室ID
and ( y.start_time between y2.start_time and y2.end_time or
y.end_time between y2.start_time and y2.end_time or
y2.start_time between y.start_time and y.end_time or
y2.end_time between y.start_time and y.end_time ) )
return @i
end;
go

alter table 会議室予約 add constraint chk_会議室予約_time check ( dbo.chk_会議室予約() = 0 )
go

かなり無槍やりですね。

回答No.2

nora1962

今回のような場合、INSERTを使ってのチェックはどの方式でも無理があります。
【追加データ１】を追加しようとするトランザクションが開始し、INSERTされても、続いて開始された【追加データ２】のトランザクションからは【追加データ１】のトランザクションがCOMMITされないと【追加データ１】のデータは見えません。
やるとするならば、予約テーブルを会議室コード+時間単位を主キーにあらかじめ登録しておき、UPDATEで更新をかけるやり方です。
これだと、
【データ１】の範囲をSELECT WITH(UPDLOCK)で読み取ります。
【データ２】の範囲をSELECT WITH(UPDLOCK)で読み取ろうとします。（ここでWAITがかかる）
【データ１】の範囲をUPDATEし、COMMITすろとロックが解除され【データ２】の範囲を読み取りますが既に更新がされているのでUPDATEをしないようにアプリケーションで判断できます。

または
【データ１】の範囲をSELECTで読み取ります。
【データ２】の範囲をSELECTで読み取ります。
【データ１】の範囲をトランザクションを開始して再度読み取ります。更新スタンプが変わっていないことを確認してから更新し、COMMITします。
【データ２】の範囲をトランザクションを開始して再度読み取ります。更新スタンプを確認すると更新されているので更新を中断する。
てな感じですかね。

補足

１件１件のデータ追加間隔は十分な時間があるとした場合、何か方法はありませんでしょうか？

回答No.1

bin-chan

【方法Ａ】check制約のようなDB依存しない方法で、テーブルにあらかじめチェックを加える。

「Insertしたいデータ（※１）の開始時刻を上回る終了時刻を持ち（※２）、
かつ、その（※２）開始時刻がInsertしたいデータ（※１）の終了時刻を下回るレコード」を
検索して、一件でもあれば「時刻が重複します」ですよね。

補足

申し訳ありませんが、チェックロジックではなく、チェックの実装方法についての質問ですので、よろしくお願いします。