How do you choose a primary key in access?

There are many ways to make a primary key or have one automatically generated in Microsoft Access. For additional information about selecting or creating a primary key watch this video which walks through a few examples and discusses various ways to create a custom key for your Access table.

After watching the video ask yourself a few questions and discuss your answers to them.

You can view the transcript for “How to Select and Make PRIMARY KEYS in Microsoft Access” here (opens in new window).

Discussion Questions

  • Why is it important to choose a memorable primary key?
  • Is adding a description important for a primary key? Why or why not?
  • In what types of scenarios would you use this information?

Share your opinions below and respond to two of your classmates’ thoughts.

Don't use a ZIP code or Social Security number

Updated on September 11, 2020

Databases depend upon keys to store, sort, and compare or create relationships between records. If you’ve been around databases for a while, you’ve probably heard about different types of keys: primary keys, candidate keys, and foreign keys.

When you create a new database table, you’re asked to select one primary key that will uniquely identify each record stored in that table.

Why a Primary Key Is Important

The selection of a primary key is one of the most critical decisions you’ll make in the design of a new database. The most important constraint is that you must ensure that the selected key is unique. If it’s possible that two records (past, present, or future) might share the same value for an attribute, it’s a poor choice for a primary key.

Another important aspect of a primary key is its use by other tables that link to it in a relational database. In this aspect, a primary key acts as the target of a pointer. Because of these interdependencies, a primary key must exist when a record is created, and it can never change. 

Poor Choices for Primary Keys

What some people might consider an obvious selection for a primary key could be a poor choice instead. Here are a few examples:

  • ZIP codes do not make good primary keys for a table of towns. If you’re making a simple lookup table of cities, ZIP code seems to be a logical primary key. However, upon further investigation, you might realize that more than one town shares a ZIP code. For example, the New Jersey cities of Neptune, Neptune City, Tinton Falls, and Wall Township all share the 07753 ZIP code.
  • Social Security numbers do not make good primary keys for many reasons. Most people consider their SSN private and do not want it clearly visible to database users. In addition, some people don’t have SSNs.
  • Email addresses are also a poor choice for a primary key. Although they are unique, they could change over time. Furthermore, not everyone has an email address.

What Makes a Good Primary Key

So, how do you choose an effective primary key? In most cases, turn to your database system for support.

A best practice in database design is to use an internally generated primary key. Your database management system can normally generate a unique identifier that has no meaning outside of the database system.

For example, you might use the Microsoft Access AutoNumber data type to create a field called RecordID. The AutoNumber data type automatically increments the field each time you create a record. While the number itself is meaningless, it provides a reliable way to reference an individual record in queries.

A good primary key is usually short, uses numbers, and avoids special characters or a mix of uppercase and lowercase characters to facilitate rapid database lookups and comparisons.

Thanks for letting us know!

Get the Latest Tech News Delivered Every Day

Subscribe

Download PC Repair Tool to quickly find & fix Windows errors automatically

When you create a Microsoft Access table, Access will automatically create a primary key to your database table, but you can also specify the field you want as the primary key for your database table.

The Primary key in Microsoft Access is a field or set of fields with unique values throughout the table. The primary key offers several characteristics, such as it uniquely identifies each row in the database, It always contains a value, it is never empty, and the value contains never change.

To add and remove a Primary Key from an Access database table, follow the methods below.

1] How to remove the default Primary key from Access Table

Open the database that you want to alter.

How do you choose a primary key in access?

On the navigation pane on the left, click the table that contains the field size you want to change.

Then click the Design view.

By default, Access will add a Primary key to your table, but in some cases, you would want a different field as the primary key.

How do you choose a primary key in access?

Change the field’s data type with the default primary key, from Auto-Number to Number, by clicking the drop-down arrow of the Data Type and choosing the Number Data Type.

How do you choose a primary key in access?

Then right-click the Primary Key icon and select Primary Key.

The other method to remove the Primary key is to click the  Primary Key button on the menu bar, and the primary key is removed from the field.

Then click Save on the Quick Access toolbar.

2] How to add a new Primary Key to the Access table

We will add the new primary key to the field that we want to become the primary key.

Delete the field you want to become the primary key because once you enter data in a table, you cannot change the data type of any field to AutoNumber even if you have not added any data type to the field.

The AutoNumber field makes an excellent primary key, and Access does not allow two AutoNumber data types in an Access table, only one.

Add a new row to the table and name the field the name of the field you deleted.

Make the field an AutoNumber data type.

How do you choose a primary key in access?

Then right-click the field and select Primary Key from the context menu.

The other method to add the Primary key is to click the  Primary Key button on the menu bar.

Then click Save after the primary key is added to the field.

Can we have two Primary keys in Access table?

No, you cannot have two primary keys in a table. Each table in Microsoft Access can only be able to have one primary key.

What is the purpose of the Primary key in Access?

The primary key’s purpose is to promptly associate data from multiple tables and combine that data in a meaningful way.

We hope this tutorial helps you understand how to add and remove Primary Keys from an Access database table.

How do you choose a primary key in access?

Shantel is a university student studying for Bachelor of Science in Information Technology. Her goal is to become a Database Administrator or a System Administrator. She enjoys reading and watching historical documentaries and dramas.