What is a Primary Key and Its Importance?

A primary key is essential for uniquely identifying each row within a database table. To illustrate this concept, consider a table created to keep track of customer information.

Everything functions well up to this point. Now, imagine a scenario where John Smith visits your store and requests to update his contact number to “+614500003.” This can be accomplished using an update query that references both his first and last names:

UPDATE Customers

SET ContactNum = ‘+614500003’

WHERE FirstName = ‘John’ and LastName = ‘Smith’;

However, issues may arise if there are multiple customers sharing the same first and last names.

In such cases, additional identifiers like the date of birth would be necessary to differentiate between them. By implementing a unique primary key for each row, this complication can be resolved.

In the example above, the CustomerID serves as a self-incrementing primary key, assigning a distinct value to every newly added customer.

What is a Foreign Key and Its Importance?

Let’s explore a different scenario involving a grocery store, where two key functions are required: managing orders and tracking inventory. For the order management aspect, we can establish an Order Table.

As previously mentioned, having an ID column that acts as a primary key is crucial. In this table, I have also included an OrderBundleID, which enables us to group multiple products purchased in a single transaction.

Now, let’s examine how a couple of orders might appear in this table.

Upon closer inspection, you will notice that certain details, such as OrderBundleID, OrderDateTime, and TotalOrderValue, are repeated across both orders. This design is not optimal.

There exists a relationship between an Order Header and its Line Items, as each order can encompass multiple line items. By separating the Order Header and Line Items into distinct tables, we can eliminate redundant information. This is where the interplay of primary and foreign keys becomes advantageous.

By organizing the sample orders into these tables, the data structure becomes significantly more streamlined.

In summary:

• Primary Keys are crucial for uniquely identifying records within a table.
• Foreign Keys establish a relationship between parent and child records, significantly reducing data redundancy.

Not a member yet? Click the link below to gain access to unlimited articles on Medium and the opportunity to write and earn.