Null Primary Key Possible?

[Ken]

I am using an existing table with hundreds of records in a much more active
way with several employees, so I would like to define a primary key to avoid
duplicates. The primary key has to be 6 fields, because obviously the
records can contain the same info in any of the 6, but not all of the 6. My
problem is not all of the fields have values all of the time.
Does anyone know of a work around? I keep looking at "Primary key cannot
contain a null value"
Any help is greatly appreciated

 

[Allen Browne]

Access does not permit a null in a pk, but a 6-field pk sounds a little
unweildy to me--particularly if you might then need to use those 6 fields as
a foreign key in another table.

You can still create another index on the combination of the 6 fields
(Indexes on View menu in table design.)

Another solution might be to set the AllowZeroLength property to Yes for
these fields (assuming Text type) and set DefaultValue to "". This
circumvents the issue with null, and the no-duplicates policy still works.
All things considered, this would probably not be a good design choice.

 

[Tim Ferguson]

The primary key has to be 6 fields, because obviously the
records can contain the same info in any of the 6, but not all of the
6. My problem is not all of the fields have values all of the time.

As a dyed-in-the-wool opponent of artificial keys, I reckon that this
sounds like an appropriate use of one... A PK has to be stable, unique and
knowable. If your key is not knowable then it's not a good choice.

Hope that helps

Tim F

 

[Kevin K. Sullivan]

Null is different than a single, specific value.

1 = 1 evaluates to TRUE
1 = 3 evaluates to FALSE
1 = NULL evaluates to NULL

Comparisons of the records containing null values will be *ambiguous*.
Therefore, fields must not have Nulls if they are to have a unique index.

If you want to use a specific, out-of-range value, such as a zero-length
string or a negative number to represent "different than the others",
you can build an index. But is that really worth it?

HTH,

Kevin