Re: Unique constraint and NULL values
From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 21 Oct 2004 22:07:35 -0700
Message-ID: <1098421602.644915_at_yasure>
>
>
> Not really. The SQL standard consistently defines all the nullable
> constraints (CHECK, UNIQUE, FOREIGN KEY) in the negative. In other words,
> the constraint doesn't permit violations (logical FALSE) but nor does it
> require that the constraint be satisfied (logical TRUE). The UNKNOWN case is
> not considered a violation of a constraint, which is why NULLs are
> permitted. Unfortunately Microsoft disagrees ;-)
Date: Thu, 21 Oct 2004 22:07:35 -0700
Message-ID: <1098421602.644915_at_yasure>
David Portas wrote:
>>yes, in theory, the SQL Server approach is more 'pure'
>
>
> Not really. The SQL standard consistently defines all the nullable
> constraints (CHECK, UNIQUE, FOREIGN KEY) in the negative. In other words,
> the constraint doesn't permit violations (logical FALSE) but nor does it
> require that the constraint be satisfied (logical TRUE). The UNKNOWN case is
> not considered a violation of a constraint, which is why NULLs are
> permitted. Unfortunately Microsoft disagrees ;-)
Actually that isn't entirely true. SQL Server, in order to claim ANSI compliance has a parameter that allows it to work as does Oracle. It just isn't the default. And since most SQL Server types can't or don't read manuals I can't think of a single instance where I've seen it set for ANSI compliance.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace 'x' with 'u' to respond)Received on Fri Oct 22 2004 - 07:07:35 CEST