Choose primary key

Which data type is best for a unique field within a table like a primary key?

Ganesh Chandrasekaran

Apr 19, 2020

4 min read

Member-only

Easy way select the right column as Primary Key for a given table

A primary key is a field in a table which uniquely identifies each row/record in a database table.

Before you read further, what are the attributes of a Primary Key ?

Unique & Not Null.
Primary key can consist of single or multiple columns.
One Primary Key per table.

Take a look at this Table, now select which column(s) best suited for Primary key ?

In relational database tables, we use ‘keys’ to identify and find information more easily, or to show the relationships between the data of two different tables. Two of these identifiers are primary keys, and unique keys. Let’s take a closer look.

Make your own ER diagram in Gleek.

What is a primary key?

Primary keys are a column or set of columns in a relational database table that uniquely identify items in the rows of the table. Each primary key entry identifies one, and only one, item. Let’s say we have a table with people in a database. They each have a name, and an ID number. While two people may have the same name, no two can have the same ID number. In this example, the ID number is the primary key.

Read also Primary key vs Foreign key comaprarison.

What is a unique key?

Unique keys are columns in a relational database table that uniquely identify items in the rows. This sounds a lot like a primary key, but the main difference is that unique keys can have NULL values. Their purpose is to prevent duplicate items in rows, but the items in the row also don’t need to have a value.

Going back to our ID example, let’s say that there is a group of people at the post office, and each of them has a ticket that tells them when they can go to the window. Maybe someone is at the post office with their friend, and they don’t have a ticket. Their ID number is still the primary key, and the ticket number is a unique key, because it is possible for someone to not have a ticket, and no two tickets have the same number.

When primary keys are used

Primary keys are used in ‘parent’ tables, which other tables can be linked back to. They are used for identifying every item in a table that does not have a NULL value, and for creating a clustered index. It is important to note that relational tables cannot function without a primary key, because it becomes nearly impossible to find information in the table. Changing the data of primary keys should also be done with care, as it can cause major issues in the table.

When unique keys are used

Unique keys are used to prevent any duplicate values in a table, but when the value can also be NULL. There can be more than one unique key, and they can easily be changed or removed, unlike a primary key. Unique keys, by nature, create non-clustered indexes, and do not support increment values.

The main differences between primary and unique keys

Though quite similar on the surface, the functions of primary and unique keys differ, making them both a vital part of relational database tables. Primary keys are usually incremental in value, and create clustered indexes, while unique keys are the opposite. There can be one or many unique keys, while there can only be one primary key. Both types of key can exist within the same table. Unique keys are used when we want to identify specific items, and do not want any duplicate values. Primary keys are also used to uniquely identify items, but every row must have a primary key, while it is possible for the row to be lacking a unique key.

Make your own ER diagram in Gleek.

Looking for a new diagramming tool? Try Gleek – a text-based tool you can use to create ER and UML diagrams with just a few keystrokes. Get started for free here.

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.

The primary key uniquely identifies the employee ID, which correlates to a specific employee.

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.

Three types of database keys defined

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.

Which data type can be used as primary key field?

Often, a unique identification number, such as an ID number or a serial number or code, serves as a primary key in a table. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key.

Which option is correct for unique key and primary key?

Primary key will not accept NULL values whereas Unique key can accept NULL values. A table can have only one primary key whereas there can be multiple unique key on a table. A Clustered index automatically created when a primary key is defined whereas Unique key generates the non-clustered index.

What data type must have unique keys?

A relational database table may have one or more available unique keys (formally called candidate keys). One of those keys per table may be designated the primary key; other keys are called alternate keys.

Is primary key a unique field in a table?

The primary key is accepted as a unique or sole identifier for every record in the table. In the case of a primary key, we cannot save NULL values. In the case of a unique key, we can save a null value, however, only one NULL value is supported.