db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oystein.Grov...@Sun.COM (Øystein Grøvlen)
Subject Re: Unique constraints on multiple fields with one nullable
Date Fri, 28 Oct 2005 05:03:12 GMT
>>>>> "MJS" == Michael J Segel <msegel@segel.com> writes:

    MJS> By the definition of a UNIQUE TABLE CONSTRAINT, I should get
    MJS> only a single row returned when I query on the tuple that are
    MJS> part of the defined constraint.

This is generally not true.  It is only true for equality expressions.
For a range expressions, you may of course get more than a single row.
Since NULL values can not be tested by equality expressions in SQL
(you have to use 'IS NULL'), I think your logic is faulty.

    MJS> Using our existing example, If I say the following:
    MJS> SELECT * 
    >> FROM foo 
    MJS> WHERE id = 1
    MJS> AND     name = "AAA";

    MJS> I should expect that I will get back at the most 1 record.

    MJS> Now why is Sybase a bug?
    MJS> Suppose we had the following:

    MJS> SELECT * 
    >> FROM foo 
    MJS> WHERE id = 1
    MJS> AND name IS NULL;

    MJS> How many rows will I get back?
    MJS> (Again its rhetorical...)

I do not think this example is relevant since the where-clauses are
not of the same type.  The latter query does not contain an equality
predicate for name. 

I think your view limits the expressiveness of the SQL language.  In
my view, NOT NULL and UNIQUE are orthogonal constraints:

    NOT NULL - All rows must have a value for this column
    UNIQUE - All values for this column must be unique

This will give you four possible combinations.  The combination of
UNIQUE and NULLs allowed becomes meaningless with your interpretation
since it would mean that all rows, except one, must have distinct
values, and there will be not way to express 'nulls allowed and all
values should be unique'.

The main point is that NULL is not a value.  It represent the absence
of a value.       


View raw message