Multifield Pri Key + Nulls ?

[TC]

Can you set Required=No for the individual fields within the multi-field
primary key? (I don't have Access here to check)

HTH,
TC

 

[root]

A primary key can't be null which makes sense.

A multifield primary key where the whole key can't be null makes sense.

A multifield primary key where no single field of the multifield key can be
null does not seem to make sense. What gives? Is there a way around this
restriction in Access2000?

 

[Armen Stein]

Can you set Required=No for the individual fields within the multi-field
primary key? (I don't have Access here to check)

HTH,
TC

No part of a primary key can be Null.

If a field can be null, it doesn't belong in a primary key. Instead,
make an AutoNumber key, and let all the other fields be regular data
fields. Use the AutoNumber key for all your joins to other tables.

Hope this helps,

 

[Tim Ferguson]

A multifield primary key where no single field of the multifield key
can be null does not seem to make sense.

Yes it does. A PK is like an address for a record. A null is a very special
value: it is not equal to itself, and it is not necessarily different from
another known value. So record (2003, "NY", 43) might or might not be a
duplicate for (2003, NULL, 43) -- the db engine has no way of knowing.

Hope that makes things a bit clearer.


Tim F

 

[root]

Yes it does. A PK is like an address for a record. A null is a very special
value: it is not equal to itself, and it is not necessarily different from
another known value. So record (2003, "NY", 43) might or might not be a
duplicate for (2003, NULL, 43) -- the db engine has no way of knowing.

Is there any way to make a blank entry as opposed to a null such that a key
of the form (2003,,43) or (2003,"NY",,) is produced? Other index systems
I've dealt will allow such.

 

[TC]

Armen seems to be saying that from a conceptual viewpoint, it does not make
sense for any field within a multi-field primary key to be null - even if
that was allowed.

I for one have never quite resolved this particular issue in my own mind, so
I'd like to see a real-life example where someone says that it *does* make
sense for one field of a composite primary key to be null.

So, can you say more about your own example? What is the table & columns in
question?

TC

 

[John Vinson]

Is there any way to make a blank entry as opposed to a null such that a key
of the form (2003,,43) or (2003,"NY",,) is produced? Other index systems
I've dealt will allow such.

A Text field can be set with its Allow Zero Length property True, its
Required property True, and its Default property equal to "" (an empty
string). This is permissible in Primary Keys. I don't know any way to
do the same for numeric or date fields, just text.

However, you can use an Autonumber as a surrogate primary key for your
table, and create a unique Index on the three fields (a separate index
from the Primary Key index); this index's properties should be set to
Ignore Nulls.

 

[Tim Ferguson]

Hi John

A Text field can be set with its Allow Zero Length property True, its
Required property True, and its Default property equal to "" (an empty
string). This is permissible in Primary Keys. I don't know any way to
do the same for numeric or date fields, just text.

A zero-length-string is presumably equivalent to a zero or some other
"empty" surrogate. Perhaps #1900-12-31# would do for empty dates.

But I still think that the OP has a serious design problem, if he or she
thinks that it is appropriate to use a PK that might have unknown values in
it. Unique -- no; stable -- no, unless an unknown will never be discovered
later. Sounds like an ideal candidate for an artificial key, as you
suggested.

B Wishes


Tim F

 

[TC]

(snip)

A zero-length-string is presumably equivalent to a zero or some other
"empty" surrogate.

In the jet file structure, each text field has an "is null?" bit, a
data-length byte, and zero or more bytes of actual data.

"is data- actual
null?" length data
bit byte bytes

 

[Tim Ferguson]

(e-mail address removed) (TC) wrote in

In the jet file structure, each text field has an "is null?" bit, a
data-length byte, and zero or more bytes of actual data.

I was actually referring to the logical decisions. The use of a "" sentinel
value to get round the (Null = Null) and (Null <> Something) catch is
_logically_ equivalent to using a Special Number or a Special Date. There
are very real problems with doing that, just as there are problems with
Nulls. Think about the following:

Min(#1900-12-31#, #2003-05-12#, #2003-05-10#)

Avg(0,0,23,24,28)

Do you think these give the correct answers, or would they be better with
Nulls?

Best wishes


Tim F

 

[John Vinson]

I was actually referring to the logical decisions. The use of a "" sentinel
value to get round the (Null = Null) and (Null <> Something) catch is
_logically_ equivalent to using a Special Number or a Special Date.

The one (very rare) case that I can think of is where an empty string
is in fact a real, valid attribute of an entity. The classic example
is a person's name (yes, I know, not a good attribute for a primary
key! but it is an example). Some people have no middle name and no
middle initial. This is a *different* situation from someone who might
or might not have a middle name - I know for a fact that Kevin
Holzhauser does not have any other name; his entry in a People table
should very plausibly have "" as the value of the MiddleName field.

I can't recall the one instance where I ended up using a ZLS as a part
of a Primary Key - I believe it was an artificial identifier
compatible with a historical system, where one of three or four fields
could either be absent or present; a 103-A-31 was a different entity
than a 103- -31 (or something analogous).

 

[Armen Stein]

Armen seems to be saying that from a conceptual viewpoint, it does not make
sense for any field within a multi-field primary key to be null - even if
that was allowed.

I for one have never quite resolved this particular issue in my own mind, so
I'd like to see a real-life example where someone says that it *does* make
sense for one field of a composite primary key to be null.

So, can you say more about your own example? What is the table & columns in
question?

TC

The reason I said that a null cannot participate in a primary key is
because I think of nulls as *unknown* values. An unknown value cannot
be a key because it is never "equal" to anything, even (paradoxically)
the Null keyword.

This concept is reinforced in VBA and SQL by the fact that FieldName =
Null is never true; instead, you must use the function IsNull to check
for null values.

Because SQL doesn't allow a direct "equals" operator against null
values, looking them up using comboboxes or other user interfaces
becomes difficult because they need special handling.

If you replace the word "Null" with "Unknown" in your thinking, then
you'll see that having a primary key with an unknown value doesn't make
sense. Meaningless keys (like AutoNumbers) are the way to go in these
cases, and in my opinion *most* cases.

End of 2 cents,

 

[TC]

The reason I said that a null cannot participate in a primary key is
because I think of nulls as *unknown* values. An unknown value cannot
be a key because it is never "equal" to anything, even (paradoxically)
the Null keyword.

This concept is reinforced in VBA and SQL by the fact that FieldName =
Null is never true; instead, you must use the function IsNull to check
for null values.

Because SQL doesn't allow a direct "equals" operator against null
values, looking them up using comboboxes or other user interfaces
becomes difficult because they need special handling.

If you replace the word "Null" with "Unknown" in your thinking, then
you'll see that having a primary key with an unknown value doesn't make
sense. Meaningless keys (like AutoNumbers) are the way to go in these
cases, and in my opinion *most* cases.

End of 2 cents,

Sorry, I just saw this.

I concur with all you say. But it might have been instrctive if the OP had
given us an actual example, of where he thought he needed this.

Cheers,
TC