Resources

Online Shop

Info

Designer
for Microsoft Access
Create complex MS Access databases without being an expert in relational
database design! Designer for Microsoft Access asks you plain-language
questions about what you want to manage with your database, and creates
the tables and relationships automatically. Free trial available

Relational Database Primary Keys

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.

About The Author

Lee Butler is a trainee web developer, basing in London, UK. He
has worked with Microsoft technologies and has built database using
Microsoft Access, SQL Server and Oracle and is currently building
dynamic websites using ASP.NET