Primary Key vs Foreign Key in SQL

Issue #67 | Dec 02, 2024

Hi !
Welcome to the new edition of Business Analytics Review !

Today we would be exploring SQL databases, focusing specifically on the concepts of Primary Keys and Foreign Keys. These fundamental components are essential for establishing relationships between tables and ensuring data integrity in relational databases .

Primary Key

• Unique Identifier: A primary key is a column (or a combination of columns) that uniquely identifies each row in a table.

• Non-Null: It cannot contain null values.

• One per Table: A table can have only one primary key.

• Purpose: Ensures data integrity by preventing duplicate records.

Example: Consider a Customers table with columns CustomerID, CustomerName, and City. CustomerID can be the primary key as it uniquely identifies each customer.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    City VARCHAR(255) 
    )

---

Foreign Key

• Reference to Primary Key: A foreign key is a column (or a combination of columns) in one table that refers to the primary key of another table.

• Relationship: It establishes a link between two tables, creating a parent-child relationship.

• Multiple per Table: A table can have multiple foreign keys.

• Purpose: Enforces referential integrity by ensuring that the values in the foreign key column match existing values in the referenced primary key column.

Example: Consider an Orders table with columns OrderID, CustomerID, and OrderDate. CustomerID can be a foreign key referencing the CustomerID in the Customers table.

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

Relationship: In this example, the Orders table is the child table, and the Customers table is the parent table. The foreign key CustomerID in the Orders table references the primary key CustomerID in the Customers table. This relationship ensures that every order is associated with an existing customer.

---

Recommended Reads on Primary Key and Foreign Key

1. What is a Foreign Key? (with SQL examples)
   A comprehensive guide explaining foreign keys with practical SQL examples.
   Read more here

2. Difference between Primary Key and Foreign Key
   An insightful comparison highlighting the distinctions and roles of primary and foreign keys in relational databases.
   Read more here

3. Primary Key vs Foreign Key
   A detailed exploration of how primary and foreign keys function within SQL databases, complete with examples and use cases.
   Read more here

Tool of the Day - MySQL Workbench

MySQL Workbench is a powerful and user-friendly graphical tool designed for database architects, developers, and administrators to efficiently work with MySQL databases. It provides a comprehensive set of features to streamline various database tasks, including -Visually design and create database models using Entity-Relationship (ER) diagrams, Forward and reverse engineer database schemas, allowing you to generate SQL scripts from models or create models from existing databases.
Learn More

Trending News in the field of Business Analytics

New AI tool generates realistic satellite images of future flooding- The method could help communities visualize and prepare for approaching storms.

Building an understanding of how drivers interact with emerging vehicle technologies - The MIT Advanced Vehicle Technology Consortium provides data-driven insights into driver behavior, along with trust in AI and advance vehicle technology.

---

We hope you found this edition insightful. If you enjoyed the content, please give us a thumbs up!
Please don’t hesitate to share your thoughts in the comments below. We look forward to hearing from you!