Day 25: Primary And Foreign Keys In SQL

Day 25 Of Learning

Image By Author

Hey guys, hope you’re good.

We’re close to the end of our SQL series. We will be ending the series on Day 28 as I have combined some topics together due to their shared meaning and application.

Today, we’ll be looking at primary and foreign keys in SQL.
So, let’s get started.

What Are Primary Keys?

A primary key is a unique identifier for each record in a table. It ensures that no two rows in a table have the same value for this key, thereby maintaining uniqueness and data integrity. A primary key must have the following characteristics:

1. Uniqueness – Each value in the primary key column must be unique across all records.

2. Non-nullability – A primary key column cannot have NULL values.

3. Immutability – Once assigned, a primary key value should not be changed.

Lets take an example. I’m creating a table where CUSTOMERID is the primary key. So I’ll be writing the code as below:

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100),
Phone VARCHAR(15));

CUSTOMERID is something that has to be unique for our list of customers and is non-nullable and cannot be modified. That is why we consider it to be our primary key.

Primary key can also be created on 2 columns calling it a composite primary key. Here’s how to declare 2 columns a primary key

primary key (ORDERID, CUSTOMERID)

What Are Foreign Keys?

A foreign key is a column (or set of columns) in one table that establishes a link to the primary key in another table. It ensures referential integrity, meaning that the value in the foreign key column must match a valid primary key value from the referenced table or be NULL.

Lets say we have two tables, an orders table and a customers table. Both tables have the CUSTOMERID one can be used a foreign key while the other a primary.

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

Our primary key from the customers table is now related to the foreign key in orders table.

SQL Applications

Primary and foreign keys are fundamental for data integrity, efficient querying, and establishing relationships between tables. Some key applications include:

1. Data Normalization – Prevents redundancy and organizes data into separate tables.

2. Referential Integrity – Ensures that relationships between tables remain valid.

3. Indexing for Performance – Primary keys are automatically indexed, leading to faster search and retrieval.

4. JOIN Operations – Foreign keys enable SQL JOIN operations to combine data from related tables.

I put in a great effort to provide you with guidance. I put out free content for your learning. If you’re willing you can buy me a coffee but its totally up to you, no compulsion.

---

I am a BI Engineer working at a Data And AI Firm. I’m trying to help newbies in Data land their first data job and impact as many fellow data professionals as I can. Here’s my contact info:

1. ajmal7809@gmail.com

2. Linkedin

3. bideveloper_ (Discord)