db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeffrey Lichtman <swa...@rcn.com>
Subject Re: Unique constraints on multiple fields with one nullable
Date Fri, 28 Oct 2005 06:51:54 GMT
I don't have my copy of the X3H2 SQL standard in front of me, but as 
I remember, the rule for unique constraints is that the uniqueness is 
applied only to non-null values. The standard is not available 
on-line (ANSI/ISO protects its copyright vigorously), but I did find 
a web site that backs me up on this:

http://troels.arvin.dk/db/rdbms/#constraints-unique

This site quotes the following language from the standard on unique 
constraints:

"there are no two rows in [the relation] such that the value of each 
column in one row is non-null and is not distinct from the value of 
the corresponding column in the other row"

Don't laugh (or cry) - this sort of language creates a lot of jobs 
for consultants. What it means with respect to unique constraints and 
nulls is that the constraint is enforced only for rows not containing 
nulls in the constrained columns.

The logic behind this has to do with the standard interpretation of 
nulls. A null in SQL stands for "value unknown." Since the value of 
null is unknown, the result of comparing a null to anything is also 
unknown (e.g. there's no way of knowing whether a null int is equal 
to 1, since the null could stand for 1 or for any other int value). 
So boolean logic in SQL has three values: true, false and unknown. 
You can think of "unknown" as a null boolean value.

So when a column named in a unique constraint contains null, and you 
want to know whether that value violates the constraint, the answer 
is neither "yes" nor "no" - it's "I don't know". But when that value 
is inserted, it must either go into the table or not - the INSERT 
statement must either succeed or fail ("I don't know" is not a choice 
when deciding whether to accept or reject data). The SQL standard 
goes with the rule that a uniqueness constraint is only violated if 
the DBMS knows that a row contains non-unique values. Thus, multiple 
nulls are allowed.

Not all database systems follow the SQL standard closely with respect 
to unique constraints and nulls. According to the web site I quoted 
above, some of them don't allow unique constraints on nullable 
columns (the standard does allow this restriction). Other database 
systems treat nulls as distinct values for the purposes of unique 
constraints - that is, they allow only one null. The quoted site 
claims that Oracle considers it a violation for multi-column unique 
constraints if the non-null values in a row are not unique.

There are a few places in standard SQL where nulls are considered to 
be distinct values. For example, with ORDER BY nulls are supposed to 
sort together, and with GROUP BY they are supposed to group together. 
There aren't many of these exceptions to the general rule that nulls 
are supposed to be treated an unknown values that could be equal to anything.

Now, having said all this, I must add that I really object to the 
sarcastic language used by one poster in this thread. It is uncalled 
for and counterproductive to insult people who are only trying to help.


                        -        Jeff Lichtman
                                 swazoo@rcn.com
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/ 


Mime
View raw message