"Dann Corbit" <DCorbit@xxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
