Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #500 > unrolled thread

SSE 2008: Check Clause Question

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  SSE 2008: Check Clause Question Gene Wirchenko <genew@ocis.net> - 2011-07-12 13:12 -0700
    Re: SSE 2008: Check Clause Question Erland Sommarskog <esquel@sommarskog.se> - 2011-07-12 23:56 +0200
      Re: SSE 2008: Check Clause Question Gene Wirchenko <genew@ocis.net> - 2011-07-12 15:47 -0700
        Re: SSE 2008: Check Clause Question Erland Sommarskog <esquel@sommarskog.se> - 2011-07-13 10:17 +0200
    Re: SSE 2008: Check Clause Question --CELKO-- <jcelko212@earthlink.net> - 2011-07-14 07:06 -0700
    Re: SSE 2008: Check Clause Question --CELKO-- <jcelko212@earthlink.net> - 2011-07-14 07:12 -0700
      Re: SSE 2008: Check Clause Question "Fred." <ghrno-google@yahoo.com> - 2011-07-14 08:22 -0700
      Re: SSE 2008: Check Clause Question Gene Wirchenko <genew@ocis.net> - 2011-07-14 15:26 -0700
        Re: SSE 2008: Check Clause Question Erland Sommarskog <esquel@sommarskog.se> - 2011-07-15 10:58 +0200
          Re: SSE 2008: Check Clause Question Gene Wirchenko <genew@ocis.net> - 2011-07-15 11:07 -0700
        Re: SSE 2008: Check Clause Question --CELKO-- <jcelko212@earthlink.net> - 2011-07-16 06:46 -0700

#500 — SSE 2008: Check Clause Question

Dear SQL'ers:

     Is there any way around the SQL glitch that a check condition
will always succeed when the table is empty?

     I do not want to have the first row be automatically accepted.

Sincerely,

Gene Wirchenko

[toc] | [next] | [standalone]

#503

Gene Wirchenko (genew@ocis.net) writes:
>      Is there any way around the SQL glitch that a check condition
> will always succeed when the table is empty?
> 
>      I do not want to have the first row be automatically accepted.
 
Not sure what you mean. If the table is empty, there is nothing to enforce. 
That does not mean if you add a row, that anything will be accepted.

Could you clarify?

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#505

On Tue, 12 Jul 2011 23:56:34 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      Is there any way around the SQL glitch that a check condition
>> will always succeed when the table is empty?
>> 
>>      I do not want to have the first row be automatically accepted.
> 
>Not sure what you mean. If the table is empty, there is nothing to enforce. 
>That does not mean if you add a row, that anything will be accepted.
>
>Could you clarify?

     That is what I have read.  There is even an example purporting to
show this
          http://msdn.microsoft.com/en-us/library/ms188258.aspx
It appears to be a false alarm.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#507

Gene Wirchenko (genew@ocis.net) writes:
>      That is what I have read.  There is even an example purporting to
> show this
>           http://msdn.microsoft.com/en-us/library/ms188258.aspx
> It appears to be a false alarm.
 
So it applies to that particular example, which I don't really like. 
Particularly, I don't think it is a good idea to call UDFs from CHECK 
constraints. I put all such checks in triggers.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#519

On Jul 12, 3:12 pm, Gene Wirchenko <ge...@ocis.net> wrote:
> Dear SQL'ers:
>
>      Is there any way around the SQL glitch that a check condition
> will always succeed when the table is empty?
>
>      I do not want to have the first row be automatically accepted.
>
> Sincerely,
>
> Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#520

On Jul 12, 3:12 pm, Gene Wirchenko <ge...@ocis.net> wrote:
> Dear SQL'ers:
>
>      Is there any way around the SQL glitch that a check condition
> will always succeed when the table is empty?
>
>      I do not want to have the first row be automatically accepted.
>
> Sincerely,
>
> Gene Wirchenko

Glitch? That is a law of logic; anything you say about non-existing
thigns is true. Look up "Existental import" in a text book on the
history of modern logic. And teh first row is not always accepted.

 I have some situations with self-references to avoid gaps in the
dates of a history that I start with CHECK() constraints off, insert a
first row, turn teh DRI back on and proceed to insert as usual.

[toc] | [prev] | [next] | [standalone]

#521

On Jul 14, 10:12 am, --CELKO-- <jcelko...@earthlink.net> wrote:
> On Jul 12, 3:12 pm, Gene Wirchenko <ge...@ocis.net> wrote:
>
> > Dear SQL'ers:
>
> >      Is there any way around the SQL glitch that a check condition
> > will always succeed when the table is empty?
>
> >      I do not want to have the first row be automatically accepted.
>
> > Sincerely,
>
> > Gene Wirchenko
>
> Glitch? That is a law of logic; anything you say about non-existing
> thigns is true. Look up "Existental import" in a text book on the
> history of modern logic. And teh first row is not always accepted.
>
>  I have some situations with self-references to avoid gaps in the
> dates of a history that I start with CHECK() constraints off, insert a
> first row, turn teh DRI back on and proceed to insert as usual.

That is not quite accurate. In an empty universe all universally
quantified propositions are true.  But, all existentially quantified
propositions are false.

The assetion thet the table t is not empty could be reporesnted as (Ex)
(x in t & x=x).  However, constraints are always universally
quantified over the table. A constraint C(x) on the table t is
interpreted as (x) (If x in t then C(x)).

If we apply the non-empty assertion as a constraint it will be
interpreted as (y)(If y in t then (Ex)(x in t & x=x)), which is, of
course, true if the table is empty, even though the consequent we
thought we were testing, (Ex)(x in t & x=x) is false.

I believe that there is a glitch, but it is in the documentation which
fails to document the underlying logic of the SQL constructs, probably
on the conflicting grounds that it is (1) intuitively obvious, and (2)
confusing.

Fred.

Fred.

[toc] | [prev] | [next] | [standalone]

#526

On Thu, 14 Jul 2011 07:12:12 -0700 (PDT), --CELKO--
<jcelko212@earthlink.net> wrote:

>On Jul 12, 3:12 pm, Gene Wirchenko <ge...@ocis.net> wrote:
>> Dear SQL'ers:
>>
>>      Is there any way around the SQL glitch that a check condition
>> will always succeed when the table is empty?
>>
>>      I do not want to have the first row be automatically accepted.

>Glitch? That is a law of logic; anything you say about non-existing
>thigns is true. Look up "Existental import" in a text book on the

     It would be a glitch if it turned out to be truth.  A vacuous
truth is irrelevant.

>history of modern logic. And teh first row is not always accepted.

     I have now read that it is in two places.  Given the weirdnesses
and dysfunctionalities of SQL, it was not unbelieveable.  I am glad it
is wrong.

> I have some situations with self-references to avoid gaps in the
>dates of a history that I start with CHECK() constraints off, insert a
>first row, turn teh DRI back on and proceed to insert as usual.

     Please expand "DRI".

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#531

Gene Wirchenko (genew@ocis.net) writes:
>      Please expand "DRI".
 
Declarative Referential Integrity. That is constraints: CHECK and FOREIGN 
KEY constraints. And also PRIMARY KEY and UNIQUE constraints, although you 
cannot turn them off in SQL Server.

In SQL Server DEFAULT is also termed as a constraint, but that is a bit 
incorrect.

DRI should be seen in oppsition to RI checks implemented in triggrs.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#536

On Fri, 15 Jul 2011 10:58:21 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      Please expand "DRI".
> 
>Declarative Referential Integrity. That is constraints: CHECK and FOREIGN 
>KEY constraints. And also PRIMARY KEY and UNIQUE constraints, although you 
>cannot turn them off in SQL Server.
>
>In SQL Server DEFAULT is also termed as a constraint, but that is a bit 
>incorrect.
>
>DRI should be seen in oppsition to RI checks implemented in triggrs.

     Thank you for the well-written definition.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#541

The way you get around this in ANSI/ISO Standard SQL is with the
CREATE ASSERTION statement. It is a CHECK() done at the schema level
instead of the table level. That is why CONSTRAINT names are globla
and noyt local to their table.

CREATE ASSERTION NotEmpty
CHECK ((SELECT COUNT(*) FROM Foobar) > 0);

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web