Primary key

What Is Primary Key?

by

In a database, keys are used to store, sort, and compare records, and also to initiate relationships between these records. The primary key of a database identifies each record within a relational table. 

Contents show

Choosing this key is an important part in maintaining a functional database. Either the primary key is a unique attribute, like the Social Security number within a table with one record for each person, or the key is generated by a database management system.

But what else is there to know about the primary key, and what are some of the most common examples? The following will break down everything you might need to know about the primary key within databases. 

Primary key

Primary Key Explained

The concept of a primary key is crucial to a relational database. Without it, and without the foreign keys, a relational database simply wouldn’t function. Its purpose is essentially to ensure that each of the records within a table can be identified.

The reason this is so necessary is because, in a lot of cases, a table will contain many, many records- and a lot of these records will be duplicates. 

If a key comes from observables or attributes from the real world, this would be referred to as a natural primary key. If a key is assigned arbitrarily to a record, then this would be referred to as a surrogate primary key. Most people are dealing with the aforementioned natural primary keys on a daily basis.

Examples of a Primary Key

Commercial Bank

There are many examples of primary keys in day-to-day life. One of these would be within the database of a commercial bank.

 One of the tables within a database of this kind would have a title like ‘CUSTOMER_MASTER’, and this table would contain all of the basis and static data pertaining to the bank’s customers. This information would include a customer’s name, their date of birth, their address, their Social Security number, as well as other relevant details. 

The table likely entitled ‘ACCOUNTS_MASTER’, on the other hand, would store the data of the bank accounts themselves. This data would include things like the type of account, the date on which the account was opened, the account’s withdrawal limits, as well as the information of any corresponding account). 

A primary key would be selected in order to uniquely identify each of the bank’s customers. This would entail picking a column, or more than one column, in order to ensure that no two customers will ever accidentally possess the same value.

One candidate for the ideal column for a primary key would be the column containing the customers’ Social Security numbers. 

This would only work, though, if every customer’s account contained their Social Security number, which might not always be the case. If this column was selected for the primary key and certain customers didn’t have their Social Security number held on their account, then the primary key simply wouldn’t work.

Thus, this column would not be the ideal candidate. 

Another potential solution would be to use more than one column. So, for example, you would add the customer’s surname to their date of birth, and add to that their email address. This would make for a longer primary key than is convenient, though. 

In reality, the best column for a primary key is a new column, which you would call ‘CUSTOMER_ID’. At this point the database can generate a unique number automatically for each new customer. This would be the most surefire way to guarantee unique identification. 

Once the primary key has been created, this new column would be assigned as the primary key inside the SQL script that created the table. All of the values identified as null would be rejected automatically. 

There are a number of reasons having a primary key is essential. The account number that’s designated to each of the ‘CUSTOMER_ID’s will ensure the security of the customer queries being handled, and lead to faster searches within the database.

University 

Another example of a primary key in the real world would be within the database of a university. A university will have a table in their database with the records of all of their students, and each of the student ID numbers will be unique.

This would make an ideal candidate for the primary key, because these numbers are guaranteed to be unique, and every single student will have one. 

Compare this to, say, the first name or the surname of each student, and it’s clear to see why the student ID number is the better choice when it comes to a primary key; it’s very likely that some students will have the same surname, never mind the same first name.

What are the Worst Choices for a Primary Key?

It depends on the context, but there are a number of columns that tend to be a poor choice for the primary key across the board. Some of these would include ZIP codes, employers, and email addresses. 

There are two reasons these columns might be of little use as a primary key. First, the primary key must contain a unique value for each person, immediately making ZIP codes and employers useless.

Another reason is that, although these values might be unique for each person in some instances, the values can change (for example, if someone updates their email address).

Conclusion: What is Primary Key?

The primary key within a database will be the column (or the columns) with the unique values that identify each of the rows in a table. A primary key is required for the insertion, updates, restoration, or deletion of data from a table.

This key would either be a natural primary key (a value taken from the real world, such as the student ID number column within the database of a university), or a surrogate primary key (a separate column containing an automatically generated unique value).

Regardless of the type of primary key selected, they are crucial to a functioning, efficient relational database.

https://www.youtube.com/watch?v=kG5edDFilhw
Albert Niall
Latest posts by Albert Niall (see all)