Which of the following is the best example of a primary key for a table that holds employee data?

Primary and foreign keys are the most basic components on which relational database theory is based. Primary keys enforce entity integrity by uniquely identifying entity instances. Foreign keys enforce referential integrity by completing an association between two entities. The next step in building the basic data model to:

  1. Identify and define the primary key attributes for each entity
  2. Validate primary keys and relationships
  3. Migrate the primary keys to establish foreign keys

Define Primary Key Attributes:

Attributes are data items that describe an entity. An attribute instance is a single value of an attribute for an instance of an entity. For example, Name and hire date are attributes of the entity EMPLOYEE. "Robert Thompson" and "12 April 1999" are instances of the attributes name and hire date.

The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. Every entity in the data model must have a primary key whose values uniquely identify instances of the entity.

To qualify as a primary key for an entity, an attribute must have the following properties:

  • It must have a non-null value for each instance of the entity
  • The value must be unique for each instance of an entity
  • The values must not change or become null during the life of each entity instance

In some instances, an entity will have more than one attribute that can serve as a primary key. Any key or minimum set of keys that could be a primary key is called a candidate key. Once candidate keys are identified, choose one, and only one, primary key for each entity. Choose the identifier most commonly used by the user as long as it conforms to the properties listed above. Candidate keys which are not chosen as the primary key are known as alternate keys.

An example of an entity that could have several possible primary keys is Employee. Let's assume that for each employee in an organization there are three candidate keys: Employee ID, Social Security Number, and Name.

Name is the least desirable candidate. While it might work for a small department where it would be unlikely that two people would have exactly the same name, it would not work for a large organization that had hundreds or thousands of employees. However, there is the possibility that an employee's name could change because of marriage (your primary key should never be changed). Employee ID would be a good candidate as long as each employee were assigned a unique identifier at the time of hire. Social Security would work best since every employee is required to have one before being hired.

Composite Keys:

Sometimes it requires more than one attribute to uniquely identify an entity. A primary key that made up of more than one attribute is known as a composite key. Below shows an example of a composite key. Each instance of the entity Work can be uniquely identified only by a composite key composed of Employee ID and Project ID.

Example of Composite Keys
EmployeeID ProjectID HoursWorked
01 01 100
01 02 120
02 01 75
02 03 115
03 03 140
03 04 80

Artificial Keys:

An artificial key is one that has no meaning to the business or organization. Artificial keys are permitted when:

  1. no attribute has all the primary key properties, or
  2. the primary key is large and complex

Primary Key Migration:

Dependent entities, entities that depend on the existence of another entity for their identification, inherit the entire primary key from the parent entity. Every entity within a generalization hierarchy inherits the primary key of the root generic entity.

Define Key Attributes:

Once the keys have been identified for the model, it is time to name and define the attributes that have been used as keys.

There is no standard method for representing primary keys in ER diagrams. For this article, the name of the primary key followed by the notation (PK) is written inside the entity box. An example is shown in below:

Which of the following is the best example of a primary key for a table that holds employee data?

Validate Keys and Relationships

Basic rules governing the identification and migration of primary keys are:

  • Every entity in the data model shall have a primary key whose values uniquely identify entity instances.
  • The primary key attribute cannot be optional (i.e., have null values).
  • The primary key cannot have repeating values. That is, the attribute may not have more than one value at a time for a given entity instance is prohibited. This is known as the No Repeat Rule.
  • Entities with compound primary keys cannot be split into multiple entities with simpler primary keys. This is called the Smallest Key Rule.
  • Two entities may not have identical primary keys with the exception of entities within generalization hierarchies.
  • The entire primary key must migrate from parent entities to child entities and from supertype, generic entities, to subtypes, category entities.

Foreign Keys:

A foreign key is an attribute that completes a relationship by identifying the parent entity. Foreign keys provide a method for maintaining integrity in the data (called referential integrity) and for navigating between different instances of an entity. Every relationship in the model must be supported by a foreign key.

Identifying Foreign Keys:

Every dependent and category (subtype) entity in the model must have a foreign key for each relationship in which it participates. Foreign keys are formed in dependent and subtype entities by migrating the entire primary key from the parent or generic entity. If the primary key is composite, it may not be split.

Foreign Key Ownership:

Foreign key attributes are not considered to be owned by the entities to which they migrate, because they are reflections of attributes in the parent entities. Thus, each attribute in an entity is either owned by that entity or belongs to a foreign key in that entity.

If the primary key of a child entity contains all the attributes in a foreign key, the child entity is said to be "identifier dependent" on the parent entity, and the relationship is called an "identifying relationship." If any attributes in a foreign key do not belong to the child's primary key, the child is not identifier dependent on the parent, and the relationship is called "non identifying."

Diagramming Foreign Keys:

Foreign keys attributes are indicated by the notation (FK) beside them. An example is shown above.


Primary and foreign keys are the most basic components on which relational theory is based. Each entity must have a attribute or attributes, the primary key, whose values uniquely identify each instance of the entity. Every child entity must have an attribute, the foreign key, that completes the association with the parent entity.

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.

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.

Glowimages / Getty Images

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.


  • What is a primary key in a table?

    It's the same as in a database. Databases can be expressed as complicated, data-rich tables, and whether you have a simple table or a full-fledged database, the primary key functions the same.

  • Why are primary keys important?

    Primary keys are essential in relational databases because these keys are used to compare and otherwise define relationships between individual records.

Thanks for letting us know!


Tell us why!