Re: Unique constraint and NULL values

ctcgag_at_hotmail.com wrote:
> Walt <walt_askier_at_YourShoesyahoo.com> wrote:

> > Anybody have a good rationale *why* it works this way?
>
> I have a rationale. I don't think it is any grand philosophical
> decision, but rather just laziness. If all the columns of a concatenated
> index are null, then the overall key is null. Since Oracle doesn't
> index nulls (I don't know why), then there is no way to enforce
> uniqueness of the null key even if you wanted to. OTOH, if any column of
> the composite is not null, then the overall key is not null
> (concatenation[1] seems to be an exception to the rule that ordinary
> operations on nulls return null), and it is indexed, and on a unique index
> the default would be to disallow duplication. Rather than going through
> the extra work of having unique composite indices do a special case check
> for any of the columns being null, they just left it that way.
>
> Well, that's my speculation, anyway.

That's my hunch as well - it was easier to implement that way, and once it was released with that behavior they can't change it at this point. Maybe I'm being too much of a purist, but it seems the actual behavior is less than ideal.

> [1] Yes, I know that the concatenation used in composite indices is not
> identical to ordinary string concatenation, but in this case it seems
> to operate pretty much the same way

And it may very well be implemented as a string concatenation at some low level. As you suggest, the behavior is the same. And if it walks like a duck...

-- 
//-Walt
// 
//