What Is A Primary Key? Database Tutorial & Example

It can be difficult to understand what a primary key is when you’re first learning database design.

However, after reading this guide, the concept of primary keys will become crystal clear to you.

Primary keys are an essential part of a relational database model, so it’s crucial to understand this if you intend to create or manage relational databases.

What Is A Primary Key?

A primary key is a column(s) within a relational database table that uniquely represents each record in the table. For example, the ideal primary key for a table of students would be their ID number, as this would uniquely identify each student in the table.

What Is The Purpose Of A Primary Key?

Each table within a database will have its own primary key. The main purpose of designating a primary key is to identify each unique record in a particular table.

This makes it far easier to search for any particular record in any given table. It also makes it much easier to identify a particular record that may have certain data in common with other records.

For instance, let’s say an administrator at a doctor’s office wants to find your medical records based on your name. This would prove to be difficult if you had the same name as another patient, perhaps a parent.

The solution to this problem would be to give the administrator additional data such as date of birth to further differentiate these records.

However, a better solution would be to give them a Social Security Number (SSN) as that would certainly be unique for each record.

That’s why we designate the primary key as something that’s unique for each record.

Primary Key Example

Let’s take a look inside the database for a University. One of the tables in that database would certainly be dedicated to maintaining the records of its students.

The fields within the table would probably vary slightly, but would certainly contain at least the names of each student, their unique student ID, and some contact information such as an email.

Now that we have our fields, we can designate one of the fields as the primary key. I’ve already mentioned why it’s not a good idea to choose a name as a primary key because as you can see, names aren’t always unique.

Additionally, it’s also not a good idea to choose email as the primary key, although it’s likely to be unique. That leaves us with only one option to choose: the StudentID.

Why Not Use Email As The Primary Key?

There are several reasons why it’s a bad idea to use an email address as a primary key.

1. A primary key should be static. This means something that shouldn’t change. Email addresses can be changed so they are not static.
2. Email addresses can be shared by multiple users.
3. String comparisons are slower than integer comparisons. Thus, it will take slightly longer to search for records within large databases.

Create a Primary Key In SQL

The primary key of a table can be specified when the table is created. To do this in SQL, follow these steps:

1. Create a table and give it a descriptive name.
2. Create columns and specify data types.
3. Type PRIMARY KEY ()
4. Within the parenthesis, specify which column will be the primary key

The following SQL creates a table named Students with four columns: StudentID, FirstName, LastName, and Email. The primary key then takes the first column, ID, as the primary key.

CREATE TABLE Students(
   StudentID  INT            NOT NULL,
   FirstName  VARCHAR (20)   NOT NULL,
   LastName   VARCHAR (20)   NOT NULL,
   Email      CHAR (30)      NOT NULL,   
   PRIMARY KEY (ID)
);

It’s also important that the primary key be NOT NULL. This means that the column must contain data. I.e. every student record MUST contain a student ID.

Surrogate Keys Vs. Natural Keys

Every primary key will also either be a surrogate key or a natural key.

It’s very simple to define which one it is:

Natural Key: If the field chosen as the primary key is naturally occurring, such as the name or email in the Student Table, then the primary key is considered a natural key.

Surrogate Key: If no natural field exists within a table that would be suitable as a primary key, then one can be created, such as the StudentID in the Student table.

Primary Keys Vs. Foreign Keys

Continuing with the example of the University database, let’s first look at another example then quickly define the difference between a primary key and a foreign key.

There are now three tables in the University database:

• Students – Contains records of each student. The primary key is StudentID.
• Classes – Contains records of each available class. The primary key is ClassID.
• RegisteredClasses – Contains records of all the classes that each student has registered for. It has a relation with the Students table and the Classes table. It contains each of their primary keys which then become foreign keys within this table. The primary key for the RegisteredClasses table is RegisterID.

The primary key for the Students table and the Classes table are used as fields in the RegisterID table. However, even though these fields are primary keys in their respective tables, as soon as they’re placed in another table they’re known as foreign keys.

Therefore, the table RegisteredClasses has a primary key called RegisterID and two foreign keys; StudentID and ClassID.

Primary Key – A field/column inside of a table that uniquely identifies each record in that table.

Foreign Key – A field/column inside of a table that provides a link to the data within another table.