こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

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

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

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

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

投稿日時 - 2011-11-04 12:25:44

QNo.7112604

困ってます

質問者が選んだベストアンサー

すみません訂正です。

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

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

投稿日時 - 2011-11-06 07:49:11

補足

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

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

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

投稿日時 - 2011-11-07 13:36:45

お礼

1)'roomA','2011/1/1','2011/1/3' 【登録可】
2)'roomA','2011/1/2','2011/1/4' 【登録不可】
3)'roomA','2011/1/5','2011/1/6' 【登録不可】
4)'roomB','2011/1/5','2011/1/6' 【登録可】

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

そこで条件を以下の3つでやることにしました。
・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の分もIDが増えているようなので、そうかなと思うものの、いかんせんDeleteに当たる部分がないもので、???です。。


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

投稿日時 - 2011-11-06 16:06:13

このQ&Aは役に立ちましたか?

4人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.7

書き忘れ。

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

投稿日時 - 2011-11-07 02:44:59

お礼

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

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

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

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

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


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

投稿日時 - 2011-11-07 14:15:52

ANo.6

ANo4です。

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

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

例)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」と出ています。
## いい加減なメッセージですいません。

投稿日時 - 2011-11-07 02:30:58

お礼

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

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

投稿日時 - 2011-11-07 13:41:24

ANo.4

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

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

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

投稿日時 - 2011-11-05 02:01:45

補足

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

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

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

投稿日時 - 2011-11-06 13:56:35

ANo.3

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

そうですね。
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

かなり無槍やりですね。

投稿日時 - 2011-11-04 23:57:42

ANo.2

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

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

投稿日時 - 2011-11-04 13:06:45

補足

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

投稿日時 - 2011-11-04 13:18:46

ANo.1

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

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

投稿日時 - 2011-11-04 12:50:32

補足

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

投稿日時 - 2011-11-04 13:05:25

あなたにオススメの質問