SSE 2008: Check Clause Question

#1

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

#2

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:

Quote:

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 (AT) sommarskog (DOT) 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

#3

On Tue, 12 Jul 2011 23:56:34 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Quote:

Gene Wirchenko (genew (AT) ocis (DOT) 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

#4

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:

Quote:

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 (AT) sommarskog (DOT) 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

#5

On Jul 12, 3:12*pm, Gene Wirchenko <ge... (AT) ocis (DOT) net> wrote:

Quote:

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

#6

On Jul 12, 3:12*pm, Gene Wirchenko <ge... (AT) ocis (DOT) net> wrote:

Quote:

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.

#7

On Jul 14, 10:12*am, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:

Quote:

On Jul 12, 3:12*pm, Gene Wirchenko <ge... (AT) ocis (DOT) 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.

#8

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

Quote:

On Jul 12, 3:12*pm, Gene Wirchenko <ge... (AT) ocis (DOT) 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.

Quote:

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.

Quote:

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

#9

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:

Quote:

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 (AT) sommarskog (DOT) 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

#10

On Fri, 15 Jul 2011 10:58:21 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Quote:

Gene Wirchenko (genew (AT) ocis (DOT) 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