SSE 2008: Check Clause Question
Gene Wirchenko
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
Erland Sommarskog
> 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.
That does not mean if you add a row, that anything will be accepted.
Could you clarify?
--
Erland Sommarskog, SQL Server MVP, esq...@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
--CELKO--
--CELKO--
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.
Fred.
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.
Gene Wirchenko
<jcel...@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
Erland Sommarskog
> 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.
Gene Wirchenko
<esq...@sommarskog.se> wrote:
>Gene Wirchenko (ge...@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
--CELKO--
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);