Обсуждение: When does a check constraint execute?
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
<Edmund.Bacon@elb_lx.onesystem.ca>, ebacon@onesystem.com writes:
> what? The check constraint should have prevented that insert, shouldn't it?
No, because a check constraint is only designed to check values within a
row. It's applied to the proposed row before insertion.
> 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,
Yup. The main problem with a CHECK constraint that does sub-selects is
that alterations to other rows could make it no-longer-true, but the
check won't be reapplied to discover that. It's only semantically
consistent if you restrict the CHECK to checking the values of the row
itself.
SQL99 considers this an optional feature:
1) Without Feature F671, "Subqueries in CHECK constraints", the
<search condition> contained in a <check constraint definition>
shall not contain a <subquery>.
We don't support this, and in fact disallow you from writing such a
thing. You can make an end run around the error check by hiding the
subquery in a function (as you did), but that isn't going to result
in correct behavior. At least not without a lot more logic than this
function has got.
regards, tom lane
On Fri, Mar 24, 2006 at 11:25:35AM -0700, Edmund.Bacon@elb_lx.onesystem.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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote:
> On Fri, Mar 24, 2006 at 11:25:35AM -0700, Edmund.Bacon@elb_lx.onesystem.ca wrote:
> <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>
Certainly true, I keep telling people here at work. But I was kind of
wondering why you'd "select * from" inside "exists", if you're not going
to use those values.
I tend to write "where exists (select 1 from", but now I'm curious
whether the planner handles this for you or if there's actually a
difference in meaning?
I assume it's always better to not let the planner decide these kind of
things. Try to write what you really want, instead of waving hands in
the general direction and make the planner decide what you mean ;)
Well, this is getting awfully pet-peevish, of course...
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
On Mon, Mar 27, 2006 at 03:46:17PM +0200, Alban Hertroys wrote: > Jim C. Nasby wrote: > >On Fri, Mar 24, 2006 at 11:25:35AM -0700, Edmund.Bacon@elb_lx.onesystem.ca > >wrote: > ><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> > > Certainly true, I keep telling people here at work. But I was kind of > wondering why you'd "select * from" inside "exists", if you're not going > to use those values. > > I tend to write "where exists (select 1 from", but now I'm curious > whether the planner handles this for you or if there's actually a > difference in meaning? > > I assume it's always better to not let the planner decide these kind of > things. Try to write what you really want, instead of waving hands in > the general direction and make the planner decide what you mean ;) > > Well, this is getting awfully pet-peevish, of course... It's actually highly database-dependant. Many databases know all about EXISTS (SELECT * ...) and can optimize accordingly. Other databases will do better with SELECT 1, and finally some will actually do worse with SELECT 1. I actually have a book that delves into how things like this are handled on different databases, though I don't remember the title off-hand. In any case, any difference from SELECT 1 vs SELECT * will be lost in the noise compared to getting rid of the count(*). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461