databasedev.co.uk - database solutions and downloads for microsoft access

Benefits of a Primary Key:

Have you ever placed an order with a company for the first time and then decided the next day to increase your order? You call the people at the order desk. Sometimes they ask you for your Customer Number. You tell them that you don't know your Customer Number. This happens all the time.....

So they ask you for some other personal information, generally your Postcode or telephone area code. Then, as they narrow down the list of customers, they will ask your name. Then, they will tell you your Customer Number. Some businesses use phone numbers as a unique starting point.

Database systems usually have more than one table, and these tend to be related in some manner. For example a Customer table and an Order table are related to each other via a unique Customer Number. The Customer table will always have one record for each Customer, and the Order table has one record for each Order that the Customer has made.

As each Customer in one physical person, you only need one record for the Customer in the Customer table. Each Customer can make several Orders, however, which means that you set up a table to hold information about each order (the Orders table). Each individual Order has one record in the Orders table.

Of course, you relate the Customers' Orders in the Orders table to the correct Customer in the Customer table by using a common field between both tables. In this example case, we would use the Customer Number (which is included in both tables).

When linking tables, we link the primary key field from one table (the Customer Number in the Customers table) to a field in the second (related) table that has the same structure and type of data in it (the Customer Number in the Orders table).

If the link in the second table is not the primary key field (and usually it isn't), it is known as the foreign key field.

Besides being a common link field between tables, a primary key field in Microsoft Access has the following advantages:

• A primary key field is an index that greatly speeds up queries, searches and sort requests.
• When you add new records, you must enter a value in the primary key field(s). Microsoft Access will not allow you to enter Null values, which guarantees that you will have only valid records in your table.
• When you add new records to a table that has a primary key, Microsoft Access checks for duplicate data and doesn't let you enter duplicates for the primary key field.
• By default, Access displays your data in the order of the primary key.

Primary key fields should be made as short as possible as this can affect the speed of operations in the database.