Re: UNIQUE constraint problem

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 06/14/04 

Date: Mon, 14 Jun 2004 15:11:54 +0100

> Another mistake in the standard (and T-SQL, for being consistent).
Why
> should NULL = 0 effectively evaluate to true in a CHECK constraint but not
> anywhere else? Not only is that confusing to people who don't know it,
but
> it's also totally flawed from a logical point of view... That's like the
> database server saying, "It's probably 0. Good enough for me!"

[...]

> In terms of CHECK constraints, I have a philosophical problem with
them
> allowing NULLs due to the fact that I consider this inconsistent behavior.

It's not the case that NULLs are treated differently in CHECK constraints.
The ISO SQL definition of a CHECK constraint says:

"A table check constraint is satisfied if and only if the specified <search
condition> is not False for any row of a table."

Note the words "not False". In other words an error condition is only raised
when the constraint evaluates to FALSE, not if it evaluates to TRUE or
UNKNOWN. The expression is evaluated consistently in three-value logic but
the CHECK constraint allows the UNKNOWN case. This is again consistent with
the ANSI/ISO treatment of NULLs in a UNIQUE constraint (but not in
SQLServer). 

-- 
David Portas
SQL Server MVP
--

Relevant Pages

  • Re: Dual Primary Keys
    ... Historically, nulls have also been zero, false, and empty strings. ... 'unknown' or 'does not apply' or 'the witness was unresponsive' then ... CONSTRAINT inventory_nbr__values ... nullbuster VARCHARDEFAULT '}' NOT NULL, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: So whats null then if its not nothing?
    ... >> According to the 3vl rules, whether two nulls are distinct or equal is ... it means effectively that unknown is treated as ... Clearly, you have a duplicate. ... the result is unknown and therefore the constraint is not violated. ...
    (comp.databases.theory)
  • Re: Dual Primary Keys
    ... You can't use the unique index (allowing nulls) on the two fields ... 'unknown' or 'does not apply' or 'the witness was unresponsive' then ... CONSTRAINT inventory_nbr__values ... nullbuster VARCHARDEFAULT '}' NOT NULL, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Relation Schemata vs. Relation Variables
    ... The point that I was making in the original post is that because keys can ... it means that the old rule that was used in the transition ... constraint to "pair up" tuples such that they describe the same entity ... prevent a user from inserting consistent but incorrect information, ...
    (comp.databases.theory)
  • Re: Continuum hypothesis
    ... function that satisfies this constraint. ... If one DENIES the continuum hypothesis, ... and ZF+MA is consistent with not AC (assuming ZF consistent, ... There exists a well-ordering < of the reals such that for every ...
    (sci.logic)
Loading