primary key (primary keyword)

What is a primary key?

A primary key, also called a primary keyword, is a column in a relational database table that's distinctive for each record. It's a unique identifier, such as a driver's license number, telephone number with area code or vehicle identification number (VIN). A relational database must have only one primary key. Every row of data must have a primary key value and none of the rows can be null.

The choice of a primary key in a relational database often depends on the preference of the administrator. It's possible to change the primary key for a given database when the specific needs of the users change. For example, the people in a town might be uniquely identified according to their driver's license numbers in one application, but in another situation, it might be more convenient to identify them according to their telephone numbers.

Importance of primary keys

A primary key serves a special place inside the data table of a relational database management system (RDBMS), such as a SQL server or an Oracle database. Every entity in a data model should have a primary key, which should be based on a single attribute or a group of attributes.

The following are the main benefits of a primary key:

• helps identify unique data, such as a customer ID;
• prevents duplication of records in a table;
• helps with updating or deleting only specific records;
• helps ensure that fields aren't null;
• helps set up relationships between tables; and
• ensures row-level accessibility.

Examples of primary keys

A primary key is critical to the operations of an RDBMS. A single table contains thousands of records, including duplicates. Therefore, it's imperative to use a primary key or closely related foreign key to ensure no record is ever duplicated and is always uniquely identified.

The following are a few common examples of primary keys:

Social Security Number (SSN). U.S. citizens are issued uniquely identifiable social security numbers, which can be used as a primary key in a relational database. Some organizations prefer to use SSNs, as each employee already has one and because of their uniqueness. However, due to privacy concerns, the use of an SSN can be controversial.

Vehicle Identification Number (VIN). A VIN is a good example of a primary key for a relational database for a vehicle registration system, as no two vehicles can have the same VIN.

Driver's license number. Driver's licenses are examples of primary keys, as they can officially identify each user as a licensed driver and their street address in the Department of Motor Vehicles' database.

Student ID. Students are routinely given a unique ID known as a student ID. Since each student ID is different for each student, it can be used as a primary key for a database table.

An automatically generated number. This can be an automatically generated key when a new record is inserted into a table.

How to choose a strong primary key

Strong primary keys are the foundation of an efficient database design. Database developers should keep the following best practices in mind when trying to create a primary key:

• Length. The primary key should be short and consist of one column whenever possible.
• Data type. The data type of a primary key should be a numeric, an integer or a short, fixed-width character.
• Uniqueness. A primary key must have a unique value, which implies that no other row in the table has the same value in the column.
• Simplicity. When creating a primary key, basic attributes such as whole numbers or short character strings should be used. Avoid special characters, spaces and differential capitalization to prevent confusion.
• Stability. Both the columns in the primary key and the values making up the primary key shouldn't change. For example, if any column is removed from the primary key, then the combination should stop being unique.
• Non-null data value. The data value of a primary key should be non-null and remain constant over time.
• Relevance. The data value used for a primary key should stay relevant. For example, it should mean the same thing five years from now as it does today.

What's the difference between a primary key, a composite key and a foreign key?

Keys are the fundamental elements used to form a relationship between two tables inside a relational database. There are stark differences between the three types of database keys -- primary, composite and foreign keys -- proving the complex nature of relational database standards.

The following are the main characteristics of a primary key, a composite key and a foreign key:

Primary key

• The data in a specific column is always unique.
• A database table can only have one primary key.
• It uniquely identifies a record in a relational database table.
• The value of a primary key can't be deleted from the tree structure or the parent table.
• It doesn't permit null values.

Composite key

• This type of primary key consists of two or more attributes, such as multiple columns.
• The data types of different columns could differ from each other.
• It becomes the composite key when more than a single column is used to uniquely identify each row in the table.

Foreign key

• It links two tables together. This means that the foreign key in one table may refer to the primary key of another table.
• There can be more than one foreign key in a table.
• The foreign key column accepts a null value.
• It can make a parent-child relationship in a table.
• Duplicate values can be stored in the foreign key column.

Database keys are integral components of a relational database and are used for identifying attributes to sort and organize data. Learn about the eight types of database keys, what they're used for and the differences between them.