by David Hockenbroch

Created on: December 09, 2008

Choosing an appropriate primary key can prevent many headaches, both for you and for the end user of your software. What exactly is "appropriate" may vary from one situation to another, however. Here are the key pieces of information for choosing a primary key.

A primary key is a field in a database table that will uniquely identify each row (or "record") in that table. This brings to light two obvious requirements of primary keys: each record must have a value in that field, and each value must be unique. When designing any software that will insert new records into the database, you'll have to make sure that the field used for the primary key is required. So, let's say we have a database of customer information for a store. The customers may have the option of giving you their email addresses, but some may choose not to do so. That would preclude the email address from being the primary key, since that field would be empty for many customers. Likewise, the home phone number, first name, last name, or any combination of names may not be a great idea either. There are many John Smiths in the world, so a name can be duplicated. If you have multiple customers from the same household, a phone number may also be duplicated. This would prevent the system from being able to uniquely identify a record.

One potential solution that jumps to mind is using something like a social security number as a unique identifier. While this is a field that would not be duplicated and that everyone could put information in, this is a very sensitive piece of personal information! There may come a time when you or the end user needs to search for information using the primary key, and, as much as you may trust the users, for security purposes the use of sensitive personal information as a primary key should be avoided. Your customer may not want to be caught standing in the middle of a busy store with a sales representative asking for his or her social security number either.

With those fields all eliminated, what is left? This depends on the type of database you're using. If you are making a user database for a website, they'll all have a unique login name. This could be a primary key. It's a piece of information that every record will have, will uniquely identify a record, and isn't too personal. Going back to that store customer database, do the customers have store credit account numbers? They may be used also. Although that may seem like using a piece of sensitive personal information,

• 1
• 2
• Next Page >>