"Dann Corbit" <DCorbit@connx.com> writes:
> Would the constraint not be satisfied if each combination (including
> NULL) were not also forced to be unique?
>
> I maintain that the constraint is still satisfied.
>
> So, it is satisfied if I stuff thousands of NULL values in there.
>
> And it is satisfied if I only allow a single NULL value.
You're misreading it. It's not a question of what you allow, it's a question
of what's in the table. The database *must* allow anything in the table that
would satisfy the constraint.
Of course the constraint is satisfied if there's only one NULL value. But the
constraint is also satisfied if there are more than one. So the database has
to allow you to do either of these since there's no constraint that they
violate.
> Let me also state that I agree: allowing null values in a unique index
> is ludicrous. But if it is allowed, I think forcing the combinations to
> be single valued makes more sense than allowing any number of them.
In fact allowing NULLs in columns involved in a unique constraint is an
optional feature of the SQL standard. But if it's allowed it is required to be
done the way Postgres does it.
Perhaps you're just thinking of primary keys (which, btw, are *not* allowed to
be nullable according to the spec). NULLs are much more likely in a situation
where it's not a key field, just a bit of incidental data that we know should
be unique.
Consider for example a user table where there's a phone number field. Now not
all users enter a phone number, but when they do we want to make sure it's
unique. Does it make sense to restrict the database to a single user with an
unknown phone number?
Or consider a product database. Some products have ISBNs, namely books, but
not all do. If the product has an ISBN then it really ought to be unique, no
other product should have the same ISBN. But any number of products can be
non-books and not have an ISBN.
--
greg
