Re: Unique indices and nulls

Roland,

> > ... In most (but not all) cases, (catalog, entry)
> > is unique and suffix and component will both be null. In those
> cases,
> > it is common to have an entry with (catalog, entry, null, null), as
> > well as multiple entries with (catalog, entry, suffix, component).
> > But there should never be more than one entry with (catalog, entry,
> > null, null).
> > Is there any way I can enforce this? Am I going to have to write a
> > trigger to check for duplicates?
<snip>
> Tell you the truth, my advice is to reconsider the way you're using
> NULLs. C.J. Date thinks NULLs are evil and best avoided ... your
> case may be an example of what he's driving at.

What Tom's getting at, here, is that you may wish to consider using
"zero values" (e.g. 0, or empty string, or 'None', or 'N/A', or
'1900-01-01', or similar)
instead of Nulls to hold "blank" fields. This would allow you to use
a standard UNIQUE constraint instead of a custom trigger, and be better
normal form to boot.

All DBAs are guilty of, to a lesser or greater degree, using NULL to
represent "None", even though NULL actually means "unknown" according
to the SQL spec. The rest of the SQL spec is designed to support NULLs
as "unknown", so this can lead to problems in application. Like yours.

-Josh Berkus