From: nobody <no...@so...> - 2001-02-28 00:27:46
|
Bugs #221649, was updated on 2000-11-04 21:30 You can respond by visiting: http://sourceforge.net/tracker/?func=detail&atid=109028&aid=221649&group_id=9028 Category: Core Engine Group: Confirmed Bug Status: Open Priority: 5 Submitted By: Claudio Valderrama C. Assigned to: Nobody/Anonymous Summary: Unique index allowed on NULLABLE field Initial Comment: I noticed it in 1999 (IB6 in private beta testing) but I'm not sure who discovered it. It still exists and may be rather old. create table mau(a int); commit; create UNIQUE index idx_mau on mau(a); commit; Whether this was left as a transition from Paradox (that accepts NULL PKs) or it's an oversight is what I want to confirm. As a comparation, you cannot declarate a PK or UNIQUE constraint if you don't include the NOT NULL clause on the affected field(s), so the underlying automatic index for such constraint will be always on non-nullable column(s). C. ---------------------------------------------------------------------- Comment By: Nobody/Anonymous Date: 2001-02-27 16:29 Message: Logged In: NO I'm not sure this is a bug. Its handy to allow a unique constraint on a column that allows nulls. The nice way to handle this is to allow any number of null values in the column but if the value isn't null, then it has to be unique. This is even the correct relational model operation since a null value isn't equal to anything else *even another null*. So each null value is unique! A place this is nice for example is a customer number that is optional but when entered must be unique. Other things like account numbers, login names, ... all can work this way. If you can't use a unique constraint on a column like this, then its really a pain to implement the above. I would argue that the unique constraint should be changed to allow it on nullable fields but if this is against the SQL-92 spec, then at least leave the ability to create unique indexes on null columns! PS: other databases also allow unique indexes on nullable columns (Sybase SQL Anywhere for example) and its a lack of understanding of how nulls actually work in the relational model (ie: the rule that null != anything else) that makes people think this shouldn't be allowed but it should and it is very useful! ---------------------------------------------------------------------- Comment By: Claudio Valderrama C. Date: 2000-11-04 21:55 Message: Just changed the title, because it said the opposite of what I wanted, sorry. C. ---------------------------------------------------------------------- You can respond by visiting: http://sourceforge.net/tracker/?func=detail&atid=109028&aid=221649&group_id=9028 |