Very Computer

What is the rationale for having a UNIQUE constraint treat NULLS as
equal?

How can I create a UNIQUE constraint on a column that allows multiple
rows to have NULL for that column:

CREATE TABLE x ( x NUMBER UNIQUE);
INSERT INTO x VALUES (1);     -- SUCCEESS
INSERT INTO x VALUES (1);     -- FAILURE
INSERT INTO x VALUES (NULL);  -- SUCCESS
INSERT INTO x VALUES (NULL);  -- FAILURE

I want the fourth insert statement above to succeed while still
enforcing the UNIQUE constraint for non-null columns. Is that
possible?

Thanks,
Brian

What would be the rationale behind UNIQUE if it was any different to
this? That is, what makes the first NULL different to your second
NULL?

Quote:> I want the fourth insert statement above to succeed while still
> enforcing the UNIQUE constraint for non-null columns. Is that possible?

I would not know how to do it, leave alone implementation in any real
world DBMS.

--
Andreas Bauer, baueran at in.tum.de, http://home.in.tum.de/baueran/
"Well, I would describe him as sorta human shape, he's got blue jeans, a
shirt on...." -- Derek in Bad Taste

        No rationale at all... SQL NULLs are just an implementation hack.  Cf
Christopher J Date, Fabian Pascal, Hugh Darwen, Lee Fe*an and others
-- check http://www.veryComputer.com/

--
   _
  / \  Leandro Guimar?es Faria Corsetti Dutra    +41 (21) 644 23 19
  \ /  http://www.veryComputer.com/./leandrod/        +41 (21) 216 15 93
   X   Orange Communications SA                  +55 (43) 322 89 71
  / \  Fita ASCII contra correio eletr?nico HTML              Su?a

No offense, but the poster of original question has a very legitimate point
in questioning the non-uniqueness of nulls, though more from a practical
angle rather than a theoretical one.  The rationale of having DB vendors and
standards committees treat nulls as the original poster suggests, especially
in terms of UNIQUE, would be that the definition of NULL would become more
in line with the definition of NULL in set and relational theory.

 A NULL should evaluate to some truth value that is unknown.  If you don't
know the value of two employee's middle name, do you assume the value of the
two middle names are the same?  According to the way nulls are specificied
in the standards comittee, and implemented in commercial database systems,
we do, at least semantically, assume that nulls not only are values, but are
or can be equivalent to each other.  An implementation that is at
loggerheads with the theoretical concept of null.

In a set, a NULL cannot and does not equal another NULL; and by extension,
since no two NULLs can be equal, tuples with all attribute values exactly
the same, but with the presence of one or more NULLS in an arbitrary number
of attributes would never theoretically be duplicates.

Granted, SQL and relational database implementation broke from this from
nearly the beginning, and vendors have always therefore treated the NULL as
a scalar value.  So, I guess such implementations are now a defacto standard
in terms of system implementation.  I still find issue with it from the
theoretical standpoint though.

Dan

From a pragmatic point of view, I have never had a case where I wanted
NULLs to be treated equal in UNIQUE constraints, but I have come
across many cases where I wanted the NULLs to not be counted in the
UNIQUE constraint.

As for why I think it should be the other way:

In SQL, a table/column constraint is violated when it's condition is
FALSE.  In the case of a UNIQUE constraint, the condition is "[x] NOT
IN (SELECT [column] FROM [table])". Now, "NULL NOT IN (SELECT [column]
FROM [table])" will never be TRUE or FALSE; it is UNKNOWN. Therefore,
the constraint should not be considered violated since it didn't
evalutate to FALSE.

Quote:> What would be the rationale behind UNIQUE if it was any different to
> this? That is, what makes the first NULL different to your second
> NULL?

NULL is not equal to NULL.  NULL is also not unequal to NULL.

If the test is NULL =  NULL,  the result is NULL.
If the test is NULL <> NULL, the result is NULL.

--
Regards,
    David Cressey
    www.dcressey.com

Quote:> Nulls are "unknown value" - not zero or the empty string, but unknown.
> Using unique on a null field is just silly - it is meaningless.

I agree with you on the fundamental point,  that uniqueness is not
meaningful with regard to NULLS.

But my understanding of NULL is slightly different from yours.

IIRC, NULL indicates "no value present".  One possible interpretation is
"unknown value".  But it isn't the only possible interpretation.  Another
possible interpretation is:  "not applicable".

For example,  you could have two adjacent columns, named "MARITAL_STATUS"
and "SPOUSE_ID", in some table of people.

If  "MARITAL_STATUS"  is "SINGLE" , suitably encoded, then the right value
for "SPOUSE_ID" is NULL.
But this doesn't mean that the spouse Id is unknown.  It is known not to
exist.  The need for this NULL can be
eliminated by further normalization.  But sometimes designers prefer to use
NULLs over adding one more table.

--
Regards,
    David Cressey
    www.dcressey.com

Quote:> That is an interpretation, true - but IMHO it is a quite incorrect
> one. By specifying that NULL is equivalent to (for example) "not
> applicable", you are assigning a KNOWN value to the unknown value
> content. This is not correct data management - if you know the value
> (in this case "not applicable") then a suitable and ALSO KNOWN value
> should be used rather than NULL.
> --

One can argue the correctness or incorrectness of a design that allows NULLS
in a foreign key field.

And,  as I said in my earlier post,  one can get rid of the problem, by
further normalizing.  In my example,
if there were a separate table to record the PERSON_ID,  and the SPOUSE_ID,
one could deal with the
case of a single person by simply not adding any row for the person in this
separate table.  The non existence
of an entire row should not be open to any quarrels concerning
interpretation.

So,  up to this point,  the difference between your opinion and mine is
merely one of point of view.  And there is a lot to be said for your point
of view.

Where I would take issue with you is when you call "Not Applicable"  a
"value".  It isn't a value, IMHO.  It's a conclusion drawn from the fact
that a value is not present at a given intersection of a row and a column.

And I would maintain that NULL is never a value, regardless of the
conclusion drawn  from it.  And I would further maintain that this is not a
matter of opinion, but a matter of definition.

--
Regards,
    David Cressey
    www.dcressey.com

Quote:> Where I would take issue with you is when you call "Not Applicable"  a
> "value".  It isn't a value, IMHO.  It's a conclusion drawn from the fact
> that a value is not present at a given intersection of a row and a column.

Maybe in some cases.  But other 'values' that could also be valid in place
of a value not present at a given intersection of a row and a column (what
is currently implemented by many designers) could also include 'Not Known',
'Not Given', 'Not Confirmed', 'Irrevalent', etc.  Any one of these
classifications, in my opinion, are values if they are considered part of
the domain of any particular attribute as defined by the data administrator
or user.  They are values in my opinion because they represent true facts,
and are therefore reflected in a manner consistent with something like:  The
person identified by NAME 'Jan' is of a gender type that is 'NOT KNOWN'.  A
case can be made for all the various variations that are often confused with
the NULL.

Why are they facts?  Because they meet the constraints of a domain defined
for a relation attribute and they are true predicates as reflected in the
body of a relation variable.

Quote:> And I would maintain that NULL is never a value, regardless of the
> conclusion drawn  from it.  And I would further maintain that this is not
a
> matter of opinion, but a matter of definition.

I'll agree.

I see I made a big mistake in not specifically refering to SQL
database, and in particular the ANSI SQL standard. I was really just
wanting to know the ANSI commitees rationale for this behavior. My
guess is that the ANSI SQL commitee just standardized on what current
implementations were doing at the time. Is my guess wrong?

Please keep in mind that I am asking about "SQL theory" not
"relational theory".

Thanks,
Brian

Quote:>Nulls are "unknown value" - not zero or the empty string, but unknown.
>Using unique on a null field is just silly - it is meaningless.

I guess that is true as nulls are implemented in SQL.  But I recall reading
a paper some years ago which suggested at least five different possible
meanings of "null" that one might want to distinguish.  For example, if you
have a simple table with name, address, and telephone number.  The phone
number field might be null for a variety of different reasons, e.g.

- person does not have a phone
- person has a phone but the number is ex-directory
- person is moving house, number is temporarily unavailable
- number was accidentally not recorded by the data entry system
- person has several phones, for details of which one to use at which
   time see some other field.

You might well want to distinguish between these meanings of null.
Obviously if using SQL you can't use the simple null facility built-in, but
you might want to build your own flag system.

--

Quote:

>In SQL, a table/column constraint is violated when it's condition is
>FALSE.  In the case of a UNIQUE constraint, the condition is "[x] NOT
>IN (SELECT [column] FROM [table])". Now, "NULL NOT IN (SELECT [column]
>FROM [table])" will never be TRUE or FALSE; it is UNKNOWN. Therefore,
>the constraint should not be considered violated since it didn't
>evalutate to FALSE.

You can also look at a constraint as something which must always be
true, instead of focusing on the violation. Do you like the integrity
of you data to be UNKNOWN? :-)

--
Nis Jorgensen
Amsterdam

Please include only relevant quotes, and reply below the quoted text. Thanks

Yes - write an insert trigger to check it.  And ignore the
admonishments as to whether it's "correct" to do so since you probably
don't have the luxury to theorize about it.

I was hoping for a solution that would work on both PostgreSQL and
Oracle. Oracle won't let a person create an insert trigger that issues
a SELECT statement on the table to which the trigger belongs.

Thanks anyway,
Brian

Quote:> Yes - write an insert trigger to check it.  And ignore the
> admonishments as to whether it's "correct" to do so since you probably
> don't have the luxury to theorize about it.

Hmmm.  Why ask the question in a comp.database.theory newsgroup then?  Is
"theorizing" synonymous with "admonishing"?

Dan