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

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".

Regards,
Christian

--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer