indexes vs PK

[SF]

Hi,

I have been working with Access for sometime. I have seen this group mention
a lot about indexes. What is the different between Index and Primary Key?

SF

 

[Dirk Goldgar]

Hi,

I have been working with Access for sometime. I have seen this group
mention a lot about indexes. What is the different between Index and
Primary Key?

An index is special mechanism for retrieving or sorting records efficiently
according to the value(s) in one or more fields. Special structures are
built in the database to map the indexed field values to the records
containing them. Accessing records by index is much faster than scanning
through the entire table looking for records with the requested field
values.

A primary key is an index with special characteristics. First, a
primary-key index must be unique -- the table can have no duplicate values
in this field (or fields, in the case of a multi-field primary key).
Second, the primary key cannot allow Nulls -- every record must have a value
in the primary key field(s). Third, when a particular index is designated
as the primary key, the database engine "knows" that it can always identify
any given record by the value of that index, and can use that knowledge in
various query operations.

 

[SF]

Thank you for the clear explaination on the differences. I am now clear
about this

SF

 

[Jerry Whittle]

Great info from Dirk. There is one other thing to watch out for with primary
keys. Access has a bad habit of creating indexes if they have the following
in the field name: ID; key; code; num.

If your PK field is named something like EmpID, Access will create a second,
useless index for that field. This second index will take up space and slow
things down when adding or deleting records.