It Is Important To Take Care Over Primary Key Assignment

I was watching the consumer programme Watchdog the other night and the show featured a complaint about a competition run by a paint manufacturer. The competition details stated that underneath a sticker on the tub was a unique number. The contestants had to peel off the sticker and check their number on the paint firm's website to see if they had won. This appeared very straightforward but one contestant bought two tubs of the product and she discovered that her 'unique' ID numbers were identical.

The company involved explained this inaccuracy away quite well, demonstrating that all contestants still had an equal chance of winning, but they couldn't paint over the fact that the word 'unique' had been used inappropriately in the competition instructions. This lack of uniqueness would render that paint firm's numbering system totally unsuitable for setting a primary key in Access.

A primary key is essential in a database where records of an individual entry are likely to be looked up. By way of example I shall return to my old job as the manager of a video and DVD rental store. Each individual DVD had its own unique number, and obviously this included multiple copies of the same film. This meant that it was possible to keep track of such information as which members had which films, which films were available to rent and which were overdue. Being able to identify each DVD by its unique number was essential to the smooth running of the operation.

Just what constitutes a unique identifying field is a decision that must be made every time we create a new database in Access. There may be a field within the database that already contains information that is suitable for a primary key. This could be an employee number, a membership number, or perhaps a National Insurance number. These are fine as long as the numbers allocated are unique and never repeated.

Fields such as postcode and date of birth are unsuitable for primary keys as there is a chance that they will be repeated for more than one entry. I live in a twenty-one apartment block and we all have the same postcode, and my eldest son shares his birthdate with Premier League footballers Peter Crouch and Dimitar Berbatov, so clearly neither of these are suitable fields for a primary key.

If you have a field that contains a series of unique identifying numbers and you want to make this your primary key, simply select the required field in Design View and then click on the key icon, which can be found on the Design ribbon in Access 2007, or on the Table Design toolbar in pre-2007 versions.

In the absence of any of these a simple solution is to let Access assign the primary key itself. If you try to save a database without assigning a primary key, Access will prompt you to create one automatically. Clicking Yes on this prompt will automatically create a new field with the title ID. On creating this field, Access will automatically insert a unique AutoNumber, starting from 1, to ensure that you have a field that is suitable for primary key use.

The creation of a primary key is one of the fundamental aspects of using Microsoft Access to its full capability. A training course in the use of this application will open up the vast array of features that lie beyond basic operations such as assigning a primary key. Perhaps if the competition compiler from that paint firm had done a little more research into the creation of a unique identifying number, his firm could have avoided the unwelcome publicity of having their goods appear on a consumer watchdog programme.