[Firebird-devel] [ firebird-Bugs-221649 ] Unique index allowed on NULLABLE field

[nobody <no...@so...>]

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: Claudio Valderrama C.
Date: 2001-03-01 21:45

Message:
Logged In: YES 
user_id=62823

This is the answer from SQL expert Diane Brown:
==========
I'll answer the easy part first: the ISO SQL standard says 
nothing about indexes, at all.  
(I believe the old X/open SQL standard, which has long 
since disappeared off the radar as far as I know, included 
CREATE INDEX statement, but ISO standard never has.)

In SQL92, PK constraint is violated if any value is null.

By the way, the SQL92 rules for Primary Key constraint and 
Unique constraint differ in their treatment of nulls.
==========

To be continued...

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