Access Database Design and Programming, Second Edition

NULL Values

The question of NULLs can be very confusing to the database user, so let us set down the basic principles. Generally speaking, a NULL is a special value that is used for two reasons:

• To indicate that a value is missing or unknown

• To indicate that a value is not applicable in the current context

For instance, consider an author’s table:

AUTHORS(AuID,AuName,AuPhone)

If a particular author’s phone number is unknown, it is appropriate for that value to be NULL. This is not to say that the author does not have a phone number, but simply that we have no information about the number—it may or may not exist. If we knew that the person had no phone number, then the information would no longer be unknown. In this case, the appropriate value of the AuPhone attribute would be the empty string , or perhaps the string “no phone,” but not a NULL. Thus, the appropriateness of allowing NULL values for an attribute depends upon the context.

The issue of whether NULLs should appear in a key needs some discussion. The purpose of a key is to provide a means for uniquely identifying entities and so it would seem that keys and NULLs are incompatible. However, it is impractical to never allow NULLs in any keys. For instance, for the Publishers entity, this would mean not allowing a PubPhone to be NULL, since {PubName,PubPhone} is a key. On the other hand, the so-called entity integrity rule says that NULLs are not allowed in a primary key.

One final remark: The presence of a NULL as a foreign ...