What is a primary key? In the world of databases, the primary key of a relational table uniquely identifies each record in the table. Databases use keys to compare, sort, and store records, and to create relationships between records.
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 used as a primary key must be unique. Even Social Security Numbers can change when the Social Security Administration reassigns a number to someone who has been affected by identity theft. Some people don't even have a Social Security Number. However, because both of those cases are rare. Social Security Numbers can be a good choice for a primary key.
Tips for Choosing Good Primary Keys
When you choose the right primary key, database lookups are speedy and reliable. Just remember:
Keep it short. Because the primary key is used for lookups and comparisons, a short primary key means the database management system can process it more quickly than a long primary key.
Use a number for the primary key whenever possible. SQL Server or other database management systems process 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.