Re: How to enforce uniqueness when NULL values are present?

Christian Schröder wrote:
> Berend Tober wrote:
>
>> Christian Schröder wrote:
>>
>>> Peter Eisentraut wrote:
>>>
>>>
>>>> A first step in that direction would be to rethink the apparently
>>>> troublesome use of null values.
>>>>
>>> ....Some of the limits are
>>> only valid after a given date, whereas other limits are valid all the
>>> time. How would you put this information into one or more tables? Of
>>> course, I could use a special date to indicate that a limit is valid all
>>> the time (e.g. 1970-01-01), but I don't think that this is better design
>>> than representing this with a NULL value.
>>>
>> I disagree. Using "-infinity" fits your defined needs unambiguously,
>> except that you have to use "timestamp" data type rather than just "date"
>>
> I agree that this would be a correct model for the given application.
> But wouldn't it be possible to think of a scenario where the same
> problem arises? The core of my problem is that some of the records are
> "more exactly" identified than some others. Some of them are identified
> using one field, whereas some others need a second field to be uniquely
> identified. Couldn't we construct examples for this?
> Of course, if a NULL always means "unknown", then this approach doesn't
> make sense. Where can I find an authorative definition of what NULL
> means? As I have quoted before, according to the Wikipedia (far from
> being authorative!) a NULL can also mean "not applicable".


A good read appears at
"http://www.postgresql.org/docs/techdocs.66.html", where it says, to
echo Peter Eisentraut (one of your first responders) "Using nulls to
mean not applicable can indicate you haven't normalized correctly."

I put a lot of stock in Joe Celko's "SQL for Smarties", if you want a
more authoritative reference than Wikipedia.