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
[EMAIL PROTECTED]
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
