What Is a Primary Key?

Learn what makes a good or bad primary key in a database

Social Security Cards
A Social Security Number is a great primary key option, as long as there is no more than one record per person. Glowimages / Getty Images

In the world of databases, the primary key of a relational table uniquely identifies each record in the table. Choosing the primary key in a database is one of the most important steps in the process. It can be a normal attribute that is guaranteed to be unique such as Social Security number on a table with no more than one record per person or—preferably—it can be generated by the database management system such as a globally unique identifier, or GUID, in Microsoft SQL Server.

Primary keys may consist of a single attribute or multiple attributes in combination.

Primary keys are the unique links to related information in other tables where the primary key is used. It must be entered when a record is created and it should never be changed. Each table in the database has a column or two specifically for the primary key.

Primary Key Example

Imagine you have a STUDENTS table that contains a record for each student at a university. The student's unique student ID number is a good choice for a primary key in the STUDENTS table. The student's first and last name are not good choices because there is always the chance that more than one student might have the same name.

Other poor choices for primary keys include zip code, email address and employer, all of which can change or represent many people. The identifier must be unique.

Tips for Choosing Good Primary Keys

  • Keep it short. Because the primary key is used for lookups and comparisons, a short primary key means SQL Server can process it more quickly than a long primary key.
  • Use a number for the primary key whenever possible. SQL Server processes number data types faster than character data types.
  • Keep it simple. Don't use any special characters, embedded spaces or a mix of upper and lower capitalization.
  • Never change the primary key after you assign it.

For more on selecting appropriate primary keys for a table, read Choosing a Primary Key.