CHECK制約により重複する期間を抽出したい

Question

SQLでINSERT文の実行時、テーブルのCHECK制約により下記内容を実現しようとしています。
・出発日と到着日を持つテーブルにレコードをINSERTする際、期間が重複する場合、レコードを追加できない

試したこと
コードは以下の通りです。

create function checkOverlappingDates
(@date date,@room_nbr int)
returns bit
as
begin
	return case when not exists (
		select * from hotel
		where room_nbr = @room_nbr
		and @date between arrival_date and departure_date
	) then 1 else 0 end
end
go

CREATE TABLE Hotel
(room_nbr INTEGER NOT NULL,
 arrival_date DATE NOT NULL,
 departure_date DATE NOT NULL,
 guest_name CHAR(30),
 PRIMARY KEY (room_nbr, arrival_date),
 CHECK (departure_date >= arrival_date),
 CHECK (dbo.checkOverlappingDates(arrival_date,room_nbr) = 1));

期待結果

下記INSERT文実行時、2つ目のINSERT文が「CHECK (dbo.checkOverlappingDates(arrival_date,room_nbr) = 1)」によりレコードを追加できない

insert into hotel values(1,'2022/1/10','2022/1/20','aaa')
insert into hotel values(2,'2022/1/20','2022/1/30','bbb')

実行結果
「insert into hotel values(1,'2022/1/10','2022/1/20','aaa')」実行時に、上記で設定したチェック制約によりエラーとなりレコードの追加ができない

補足情報（FW/ツールのバージョンなど）
OS: Windows10
SQL Server Management Studio: 15.0.18384.0

Comments

[maisumakun]

（誤解による質問だったので撤回）

Answer 1

CHECK制約でcheckOverlappingDates関数が実行される際には、テーブルhotelはデータがINSERTされた状態になっています。つまり「hotelテーブルにデータがINSERTされる」「CHECK制約によりcheckOverlappingDatesが実行される」「CHECK制約が失敗したため、INSERTされたデータがhotelテーブルから削除される」という順番になっているわけです。よって、hotelテーブルにデータがINSERTされていることを前提にして、checkOverlappingDates関数を再設計すればよいかと思います。

ちなみに2行目のINSERT文のroom_nbrは2になっていますが、これは1の間違いという認識でよいでしょうか? また、現在のcheckOverlappingDatesのアルゴリズムですと、次のようなユースケースはINSERTできることになりますが、大丈夫でしょうか? 「02/10-02/20の予定がある場合に、02/05-02/15の予約をINSERTする」

Comments

[mzn59]

ご説明いただき、ありがとうございました。
現在のアルゴリズムでエラーとなる原因について、理解できました。

INSERT文のroom_nbrについては、ご認識の通り1の間違いです。また、いただいたユースケースについては考慮漏れでしたので再検討します。こちらについても、ご指摘いただきありがとうございました。