Обсуждение: How to enforce uniqueness when NULL values are present?

Hi list!
Consider the following table definition:

     Column |       Type       | Modifiers
    --------+------------------+-----------
     id     | integer          | not null
     date   | date             |
     value  | double precision |

The id and date field together are some sort of primary key. As you see,
the date field is nullable. For the entries, the following should be
ensured:

   1. If a record with a given id and a null value in the date field
      exists, no other record with the same id is allowed.
   2. If multiple records with the same id exist, they must have
      different values in the date field and none of them must have a
      null value in this field.

How can I enforce these constraints?

Since primary keys must not contain nullable fields, I cannot define a
primary key. I tried to define two separate partial unique indices, one
for the records with a null value as date, one for those with a non-null
value:
    create unique index idx1 on test (id) where date is null;
    create unique index idx2 on test (id, date) where date is not null;

This ensures that at most one record with a given id and a null value as
date is possible, and that multiple records with the same id must have
different dates. However, it is still possible to insert one record
without a date and one or more records with dates, which violates my
above constraints.

My next idea was creating an own operator class which treats null values
as equal. For example, my special comparison operator =* would have the
following behaviour:
    '2007-01-01'::date =* '2007-01-01'::date -> true
    '2007-01-01'::date =* '2007-01-02'::date -> false
    '2007-01-01'::date =* null -> true (!)
    null =* '2007-01-01'::date -> true (!)
    null =* null -> true (!)

If these operators would be used when checking for uniqueness, the
records with a null date would always be equal to any record with a
non-null date; thus, it would not be allowed to insert more than one
record with the same id unless they had different non-null dates.

Unfortunately, this doesn't work. :-(  I assume that the date column is
never used at all so that my comparison operator is never asked. So what
can I do to make this work?

I hope someone has a solution for me. Many thanks in advance!

    Christian

P.S.: I'm using PostgreSQL 8.2.3

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

Christian Schröder wrote:
> How can I enforce these constraints?

I submit that you should rethink your database schema and properly
normalize it.  You are attempting to retool the algebra that underlies
the SQL functionalities into doing something they are not designed to
do, and you will, even if you manage to solve this particular problem,
be in a constant battle against the database system to get your
application to behave in a consistent manner.

A first step in that direction would be to rethink the apparently
troublesome use of null values.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> I submit that you should rethink your database schema and properly
> normalize it.  You are attempting to retool the algebra that underlies
>
I don't quite understand why this is a question of normalization. As far
as I can see, my table seems to be normalized as far as possible.
> A first step in that direction would be to rethink the apparently
> troublesome use of null values.
>
Let me give you a more concrete example of the usage of my table. The
table was as follows:

     Column |       Type       | Modifiers
    --------+------------------+-----------
     id     | integer          | not null
     date   | date             |
     value  | double precision |

Let's assume that the values in this table are some limits that are
given for different data (identified by the id). 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. Or I could split the data into
two different tables, one with the date column and one without. But then
I had to work with two tables with more or less the same meaning.
Wouldn't it be quite strange to model the same entities (the limits)
with two tables?
I know that it's always dangerous to quote the Wikipedia. Let me do it
anyway: "Attributes in tables in SQL database management systems can
optionally be designated as NULL. This indicates that the actual value
of the column is unknown _or not applicable_."
(http://en.wikipedia.org/wiki/Null_%28SQL%29) This is exactly what I
once learned about NULL values, and "not applicable" is exactly why I
use NULL values in my example.

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/10/07 18:22, Christian Schröder wrote:
> Peter Eisentraut wrote:
[snip]
> I know that it's always dangerous to quote the Wikipedia. Let me do it
> anyway: "Attributes in tables in SQL database management systems can
> optionally be designated as NULL. This indicates that the actual value
> of the column is unknown _or not applicable_."
> (http://en.wikipedia.org/wiki/Null_%28SQL%29) This is exactly what I
> once learned about NULL values, and "not applicable" is exactly why I
> use NULL values in my example.

I've never heard of NULL meaning "not applicable", but only
"unknown".  To me, "not applicable" screams "denormalized".

So, I'd put 1900-01-01 (or whatever is your application's epoch) in
those date fields with NULL values, because that's their effective date.

>
> Regards,
>     Christian
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF81kbS9HxQb37XmcRAkq/AJ0b6OkNtZZgNFOT/VGvqIWF2bI7DgCg4Emr
lW+BWZseIkg/Vdf6fpK7dOI=
=7jC/
-----END PGP SIGNATURE-----

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"

See "Table 8.13. Special Date/Time Inputs": ..."-infinity ... earlier
than all other time stamps"

Example:

CREATE TABLE my_table
(
  id int4 not null,
  the_date timestamp,
PRIMARY KEY (id, the_date)
) WITHOUT OIDS;

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"my_table_pkey" for table "my_table"

INSERT INTO my_table VALUES (1, '-infinity');

Query returned successfully: 1 rows affected, 47 ms execution time.

INSERT INTO my_table VALUES (1, '-infinity');

ERROR:  duplicate key violates unique constraint "my_table_pkey"

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

On Sun, Mar 11, 2007 at 11:09:56AM +0100, Christian Schröder wrote:
> 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".

Nowhere.  SQL's definition of NULL isn't even consistant, which is why
some people prefer to avoid it altogether.

Your particular case may be solvable with:

CREATE UNIQUE INDEX foo ON bar(key1, (coalesce(key2,'1900-01-01')));

Though I agree that -infinity would be a better fit.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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.

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sun, Mar 11, 2007 at 11:09:56AM +0100, Christian Schr=F6der wrote:
>> 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?

> Nowhere.

Well, in this context the authoritative definition is easy to find:
it's where the SQL spec says that two rows containing NULLs don't
violate a unique constraint.  SQL92 section 11.7 defines unique
constraints as requiring success of a <unique predicate>, and
section 8.9 defines <unique predicate> thusly:

         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.

            regards, tom lane

On Saturday 10 March 2007, Christian Schröder wrote:
> Let's assume that the values in this table are some limits that are
> given for different data (identified by the id). 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. Or I could split the data into
> two different tables, one with the date column and one without. But then
> I had to work with two tables with more or less the same meaning.
> Wouldn't it be quite strange to model the same entities (the limits)
> with two tables?

- SNIP -

>    1. If a record with a given id and a null value in the date field
>       exists, no other record with the same id is allowed.
>    2. If multiple records with the same id exist, they must have
>       different values in the date field and none of them must have a
>       null value in this field.
Seems to me that this is what you are looking for:

TABLE listofids:

     Column |       Type       | Modifiers
    --------+------------------+-----------
     id     | integer          | not null
     hasdates | bool            | default null
     unique(id, hasdates)

TABLE listofidsdates:
     Column |       Type       | Modifiers
    --------+------------------+-----------
     listofids_id     | integer          | not null REFERENCES listofids(id)
     date   | date             |  not null
     unique(listofids_id, date)

When there are dates, set listofids.hasdates=null. Otherwise, set it to true.
Does this seem most properly normalized? (it's how I would do it!) How could
this be done better?

-Ben

Thank you for your tips. I think I will change the tables and use some
minimal date instead of a null value to represent a constraint that is
valid all the time. An additional advantage of this approach is that I
can then make sure that the time intervals (I not only have a start
date, but also an end date) don't overlap.
Nevertheless, I think that there are some examples where a null value
does not exactly mean "unknown". But this is beyond the scope of this
discussion.

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