I've been working with a time travel table as discussed in "Developing Time-Oriented Database Applications in SQL" by Richard Snodgrass. I attempted to build a constraint for a table that disallowed overlapping time itervals as follows: test=# create table test_dates (from_date date, to_date date); CREATE TABLE test=# create or replace function no_overlaps() test-# returns boolean test-# language sql test-# as test-# $$ select not exists test$# (select * from test_dates t1 test$# where 1 < ( select count(*) from test_dates t2 test$# where (t1.from_date, t1.to_date) overlaps test$# (t2.from_date, t2.to_date)) )$$; CREATE FUNCTION Now lets test our function: test=# insert into test_dates values (date 'Jan 1, 2006', date 'Jan 31, 2006'); INSERT 0 1 test=# insert into test_dates values (date 'Feb 1, 2006', date 'Mar 1, 2006'); INSERT 0 1 test=# This should return true test=# select no_overlaps(); no_overlaps ------------- t (1 row) Good the two intervals do not overlap lets add an overlapping interval test=# insert into test_dates values (date 'Jan 15, 2006', date 'Feb 15, 2006'); INSERT 0 1 test=# select no_overlaps(); no_overlaps ------------- f (1 row) Good. Now lets remove the overlapping interval and try that and add a check constraint to test_dates test=# delete from test_dates where from_date = date 'Jan 15, 2006'; DELETE 1 test=# alter table test_dates add constraint check_overlaps check(no_overlaps()); ALTER TABLE test=# insert into test_dates values (date 'Jan 15, 2006', date 'Feb 15, 2006'); INSERT 0 1 what? The check constraint should have prevented that insert, shouldn't it? Let's make sure we haven't messed up. test=# select no_overlaps(); no_overlaps ------------- f (1 row) Nope, It looks like the check() constraint is being fired before the insert occurs. I'm wondering if that's correct? I have tried this on SQL Server 2000/2005 and both of those seem to fire the check constraint after the insert. I do not know whether this is a bug or not? I suspect that what's really happening is that I'm abusing CHECK constraints, and that this should more properly be handled by a trigger. Edmund
