Re: Unique constraint over null values

Thrasher,

> where type can be 's' for 'single' and 'x' for extended, so s should
> mean that there is only the type and data1 field, and x means that all
> fields are set.
>
> How can I set a unique constraint like CHECK (type = 's' AND UNIQUE
> (type, data1)) OR (type = 'x' AND UNIQUE (type, data1, data2)) ?
>
> The documentation says that 2 NULL values are different, so no unique
> constraint can be set over it.

Simple. Don't use NULLs. NULL means "unknown". You should use a
differnent value, such as a blank string or "N/A" or "000000000000000" to
represent "intentionally left blank".

Then you can set data2 NOT NULL and use a regular UNIQUE constraint.

BTW, you have defined your data columns as CHAR, not VARCHAR. You do know
that this implies that your data columns will be *exactly* 16 characters in
length?

--
-Josh Berkus
Aglio Database Solutions
San Francisco