Multiple field key - "allow nulls" vs. zero length string

[Rolls]

I figured out that to create an index across multiple fields that will allow
no entries in certain fields I need to first define the key (not a primary
key) and then do two things to each field in design view: 1) set default =
"" and 2) allow zero-length string = "yes".

This does what I want it to do. It lets me enter N^2-1 field combinations
where N is the number of fields in the key, and prevents duplicate records
from being entered for those combinations.

My question is: What does the "allow nulls" = yes/no selection do in the
index? It does NOT appear to do the same thing as a zero-length string as
described above.

 

[John W. Vinson]

I figured out that to create an index across multiple fields that will allow
no entries in certain fields I need to first define the key (not a primary
key) and then do two things to each field in design view: 1) set default =
"" and 2) allow zero-length string = "yes".

This is actually only required for a *Primary Key* index. A generic
non-primary key index can contain nulls.

My question is: What does the "allow nulls" = yes/no selection do in the
index? It does NOT appear to do the same thing as a zero-length string as
described above.

Correct - it's not the same! Allow Nulls (which isn't available for a PK) lets
you define a unique index on multiple fields, while allowing NULL values to be
permitted in those fields. The set {"X", 1, NULL} will be seen as being
identical to another record with {"X", 1, NULL} - which isn't strictly true,
since NULL is uninitialized and isn't equal to anything, not even to itself.

John W. Vinson [MVP]

 

[Rolls]

What I'm interested in doing is in a non-primary, multi-field key, allowing
blank fields

FName
MName
LName
Suffix

while not allowing duplicate records.

Allow nulls does not work.

Set default to "" AND allow zero-length does work.