Re: Unique indices and nulls

Roland Roberts <roland(at)astrofoto(dot)org> writes:
> ... 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?

AFAICT an SQL unique constraint will not do this for you. The spec
defines <unique constraint> in terms of the <unique predicate>,
which is defined as

2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.

so the constraint cannot be violated by rows that contain any nulls
(in the columns checked by the constraint).

You could possibly do something with a partial UNIQUE index on (catalog,
entry) where the index's WHERE condition selects only rows with suffix
and component both NULL. But this doesn't scale well if you also want
to forbid other cases that are equal-up-to-nulls; you'd end up with
a large number of partial indexes on different subsets of the columns.

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.

regards, tom lane