Creating a Primary Key in Access 2002 - dummies

Creating a Primary Key in Access 2002

By John Kaufeld

A table’s primary key is a special field in your tables. Just about every table you create should have a primary key. Why?

  • It organizes your data by uniquely identifying each record. For example, on a Customer table, the Customer Number would be the primary key — there is only one customer number 1, one customer number 2, and so on.

  • Nerds pitch a fit if you don’t.

You need to know a few rules about the primary key before running off to create one:

  • A table can have only one primary key.

  • A single table can have lots of indexes, but only one primary key.

  • Access 2002 automatically indexes the primary key field (that’s one reason that a primary key makes your database work a little faster).

  • If you create a new table without a primary key, Access 2002 automatically asks whether you want to add one.

If you say yes, the program gleefully creates an AutoNumber field at the beginning of your table and sets it as the primary key. If the first field is an AutoNumber type, Access 2002 anoints it as the primary key without adding anything else to the table.

  • Most of the time, the primary key is a single field, but in very special circumstances, two or more fields can share the job. The technical term for this type of key is a multifield key. The super-technical term for this type of key is compound key.

  • You cannot use the Memo, OLE Object, or Hyperlink field types in a primary key.

  • Although you can use the Yes/No field type in a primary key, you can have only two records (Yes and No) in such a table.

  • The primary key automatically sorts records in the table. This just keeps your tables neat and tidy.

  • Access 2002 doesn’t care where the primary key field is in the table design. The key can be the first field, the last field, or in the middle. The placement choice is all yours. For your sanity’s sake, you may want to put the key field first in a table. In fact, make it a habit (you’ll be so happy you did later).

  • All primary keys must have a name, just like the field has a name. This may come as a shock, so hold on to your seat, but Access 2002 automatically names all primary keys Primary Key.

To nominate a field for the job of primary key, follow these steps:

  1. Open the table in Design view.

    If you’re not familiar with this step, you probably shouldn’t be messing with the primary key.

  2. Right-click the button next to the field you’ve picked for the primary key.

    One of those cool pop-up menus appears.

    What makes a good key field? How do you find the right one? The top criterion for a good key field is uniqueness. The values in a key field must be unique, for example, Customer Numbers, Stock Keeping Units, Vehicle IDs, or some other field that’s different in every record? If you have that, use it! If you don’t, then create a unique field by adding an AutoNumber field to your table. This field type automatically inserts a new, unique number into each record of your table. AutoNumber even keeps track of numbers that you delete so that Access won’t use them again. Best of all, Access takes care of the details so that you don’t have to worry about programming or any special tricks to make the program work.

  3. Select Primary Key from the menu (as shown in the figure).

    A little key symbol appears in the button. The primary key is set!