データの挿入や更新を行う際蓄積されたデータとの比較をしたい、あるいはデータの整合性を確保するためにほかのデータとの関係性を見たいということがあると思います。こういうときはcheck制約においてサブクエリを利用したくなるのですが――結論から述べると、少なくともPostgreSQLではできませんでした。標準SQLでは「check制約でサブクエリを発行する」ことが認められているので、近い将来サポートされるとは思いますが……。それはさておき、上記のような要件を達成するためいろいろと試行錯誤した結果、ストアドファンクションを利用してサブクエリを発行した風に見せかけるのが手っ取り早そうという結論に至りました。
たとえばホテルの部屋の予約状況を示すテーブルbookingがあり、要素として部屋番号、予約開始日、予約終了日を持っているとします。「ある部屋がいつからいつまで予約されているのか」を記録しているというイメージです。さてホテルですからダブルブッキングは避けねばなりません。ただしある予約の開始日と別の予約の終了日が同一になることは許したい。これをcheck制約で実現しようとすると、サブクエリを発行する必要がありますが、上述したようにそれは難しいので、ストアドファンクションを利用したいと思います。
-- bookingテーブルを定義する。
create table booking (
booking_id integer primary key,
room_id integer not null,
start_day date not null,
end_day date not null,
-- 開始日は終了日より先で、開始日と終了日は同一であってはならない。
constraint invalid_period check (start_day < end_day)
);
-- ダブルブッキングを数える関数。更新したい予約の部屋番号、開始日、終了日を指定する。
-- ただしある予約の開始日と別の予約の終了日が重なってもダブルブッキングとして数えない。
create function count_double_booking(integer, date, date) returns bigint as $$
select count(*)
from booking
where booking.room_id = $1
and daterange($2, $3) && daterange(start_day, end_day)
$$ language SQL;
-- check制約を設けて、ダブルブッキングを禁じる。
alter table booking
add constraint double_booking
check (count_double_booking(room_id, start_day, end_day) = 0);
-- 正常に挿入できるデータ。
insert into booking values (1, 1, '2016-01-01', '2016-01-04');
insert into booking values (2, 1, '2016-01-04', '2016-01-05');
insert into booking values (3, 2, '2016-01-01', '2016-01-05');
insert into booking values (4, 3, '2016-01-01', '2016-01-06');
-- ダブルブッキングが生じるため挿入と更新に失敗する。
insert into booking values (5, 1, '2016-01-02', '2016-01-03');
update booking set end_day = '2016-01-05' where booking_id = 1;
/*
ERROR: リレーション"booking"の新しい行は検査制約"double_booking"に違反しています
DETAIL: 失敗した行は(5, 1, 2016-01-02, 2016-01-03)を含みます
ERROR: リレーション"booking"の新しい行は検査制約"double_booking"に違反しています
DETAIL: 失敗した行は(1, 1, 2016-01-01, 2016-01-05)を含みます
*/
余談: PostgreSQLの範囲型を初めて利用したのですが、便利ですね。「期間」「区間」を扱うのが楽になりそうです。