3 column unique index with Nulls = Yes

[Frak]

Is there a bug in Access 2003? Created a Unique Index on col1,col2,col3. With Nulls = Yes. I want the index to recognize the null on col3 to prevent the insertion of two iditinical records with the col3 null. The Unique Index dose not recognize the index. I am populating these columns with an AfterUpdate VB procedure. I cant get it to work. Help....

 

[Ken Snell]

With Nulls? The option in the Index for Nulls is "Ignore Nulls", which, for
what you want, should be set to No, not yes.

Check your index window and see if this is correct.

--
Ken Snell
<MS ACCESS MVP>

Is there a bug in Access 2003? Created a Unique Index on col1,col2,col3.

With Nulls = Yes. I want the index to recognize the null on col3 to prevent
the insertion of two iditinical records with the col3 null. The Unique Index
dose not recognize the index. I am populating these columns with an
AfterUpdate VB procedure. I cant get it to work. Help....

 

[Ken Snell]

Sorry -

After I posted this, I did a bit more checking. Null values will not be
included in the "index" regardless of your setting. You likely will need to
use a validation code in your data entry form to do this extra validation.
Not knowing your setup, let me suggest that what you'd do is run code on the
BeforeUpdate event of the form to check the three entered values; if the one
is Null (use the IsNull function to test for it), then check if the other
two are already present in the table with the third value as a Null. A bit
tricky, but doable.

 

[Tim Ferguson]

I want the index to recognize the null on col3 to prevent the
insertion of two iditinical records with the col3 null.

The problem with this kind of reasoning is that it expects two nulls to be
the same thing: the truth is that null is neither equal to, nor different
from, another null. Therefore, the database has no way of knowing whether
the record (Grey, Delta, NULL) is the same as (Gray, Delta, NULL) or not.
It is obviously not possible to build an index on this footing.

If you agree that the two records are distinct, then it is not possible to
be sure which record is which. On the other hand, it is not likely to be
safe to assume that they are the same if you don't even know either value.

I would guess that you have a Database Design Error and need to look again
at your entities.

Hope that helps


Tim F

 

[John Vinson]

Is there a bug in Access 2003? Created a Unique Index on col1,col2,col3. With Nulls = Yes. I want the index to recognize the null on col3 to prevent the insertion of two iditinical records with the col3 null. The Unique Index dose not recognize the index. I am populating these columns with an AfterUpdate VB procedure. I cant get it to work. Help....

If you need to distinguish one record with (X, Y, Null) from another
record (X, Y, Null) - YOU CAN'T. As noted elsethread, Null means more
or less "this value is unknown, undefined - it could be anything".

One getaround in this case is to have the field set to Required = Yes,
Allow Zero Length String = True, Default = "". This will store a
non-Null text value, a zero length string; this value CAN be included
in a unique Index, and you can avoid the null trap altogether.