Part I: Access Building Blocks
82
FIGURE 2.29It’s easy to set the properties of an index.
Selected index
Index propertiesThe index properties are quite easy to understand (these properties apply to single-field and com-
posite indexes equally):l (^) Primary: When set to Yes, Access uses this index as the table’s primary key. More than
one field can be designated as the primary key, but keep the rules governing primary keys in
mind, particularly those requiring each primary key value to be unique and that no field in a
composite primary key can be empty. The default for the Primary property is No.
l (^) Unique: When set to Yes, the index must be unique within a table. A Social Security
number field is a good candidate for a unique index because the application’s business
rules may require one and only one instance of a Social Security Number in the table. In
contrast, a last name field should not be uniquely indexed, because many last names, like
Smith and Jones, are very common, and having a unique index on the last name field will
only cause problems.
When applied to composite keys, the combination of field values must be unique — each
field within the composite key can duplicate fields found within the table.
l (^) Ignore Nulls: If a record’s index field contains a Null value (which happens in a com-
posite index only if all fields in the composite index are Null) the record’s index won’t
contribute anything to the overall indexing. In other words, unless a record’s index con-
tains some kind of value, Access doesn’t know where to insert the record in the table’s
internal index sort lists. Therefore, you might want to instruct Access to ignore a record if
the index value is null. By default, the Ignore Nulls property is set to No, which means
Access inserts records with a Null index value into the indexing scheme along with any
other records containing Null index values.
You should test the impact of the index properties on your Access tables and use the properties
that best suit the data handled by your databases.