Re: When does a check constraint execute?

On Fri, Mar 24, 2006 at 11:25:35AM -0700, Edmund(dot)Bacon(at)elb_lx(dot)onesystem(dot)ca wrote:
> 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)) )$$;

<pet-peeve>
If you only want to know if something exists, do NOT use count!

> test$# (select * from test_dates t1
> test$# where EXISTS ( select * from test_dates t2
> test$# where (t1.from_date, t1.to_date) overlaps
> test$# (t2.from_date, t2.to_date)) )$$;

On a small dataset you may not notice much difference, but you'll
certainly see it on a large dataset.
</pet-peeve>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461