[Term of the Day]: Primary Key

[Term of the Day]: Primary Key

Term of the Day 

Primary Key 


Definition — What is a Primary Key?

In the context of a relational database, a Primary Key is used as a unique identifier to quickly parse data within the table. None of the fields that are part of the primary key can contain a NULL value. In fact, since a table can easily contain thousands of records (including duplicates), a primary key is necessary to ensure that a table record can always have only one primary key. The choice of a primary key in a relational database often depends on the preference of the administrator. It is possible to change the primary key for a given database when the specific needs of the users change. Primary keys are used along with the foreign keys to refer to various tables and form referential integrities. Each table in the database has a column or two specifically for the primary key.

Primary Key Example: Let say a hospital has a patient's table that contains a record of each patient. The patient ID number( It a unique id) is a good choice for a primary key in the patient's table. The patient's first and last name are not good choices reason being there is always the chance that more than one patient might have the same name. Other poor choices for primary keys include ZIP code and State name all of which can change or represent many people. The thumb rule is an identifier used as a primary key that must be unique.

In some situations Complex Primary Key can also be used, where each field value by itself might not be unique, but the combination of several field values can be used as the primary key. Let say a company has many office branches in the same state, each with its own address. "City Name", "State", "Zip codes" and so on aren’t unique values, but the combination of something like Address+City name+State+Zip code might be used as a primary key. In the real world, using complex primary keys brings complications in SQL query's it is better to avoid complex primary keys.