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]

 

[Jamie Collins]

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.

Erm, isn't it rather the case that

{"X", 1, NULL} 'is identical to' {"X", 1, NULL}

evaluates to UNKNOWN, therefore does not fail to satisfy the table
constraint (uniqueness) therefore satisfies the table constraint?

For a (I hope) better explanation of constraint checking involving the
NULL value, see my post from earlier today (http://groups.google.com/
group/comp.databases.ms-access/msg/316dc134742d601e).

Jamie.

--

 

[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.

 

[Jamie Collins]

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.

I'm not sure I agree that a person can have a 'NULL name' and I've
never found a zero-length text column to be useful.

I usually use placeholders '{{NA}}' (e.g. I know that the person has
no middle name) and '{{NK}}' (e.g. if the person has a middle name I
don't know what it is).

Let's just use a generic '{{NA}}' (i.e. value is missing for whatever
reason), just to make the constraints easier to define:

CREATE TABLE Employees (
employee_ID INTEGER NOT NULL UNIQUE,
last_name VARCHAR(35) DEFAULT '{{NA}}' NOT NULL,
first_name VARCHAR(35) DEFAULT '{{NA}}' NOT NULL,
middle_name VARCHAR(35) DEFAULT '{{NA}}' NOT NULL,
name_suffix VARCHAR(5) DEFAULT '{{NA}}' NOT NULL,
CONSTRAINT employee__at_least_one_name
CHECK (NOT (
last_name = '{{NA}}' AND first_name = '{{NA}}'
AND middle_name = '{{NA}}' AND name_suffix = '{{NA}}'
)),
UNIQUE (last_name, first_name, middle_name, name_suffix)
)
;

I suggest you'll want Allow Zero Length = false (or equivalent e.g.
LEN(last_name) > 0) on those columns; actually, I also recommend other
validation rules for such text columns to prevent leading/trailing/
double spaces and allow only certain characters (e.g. disallow curly
braces while specifically allowing them in the defined placeholders).

Jamie.

--