Welcome to the third module of our course. In the previous module, we honed your ability to analyze data within a single table, using aggregation and string functions to extract insights from security logs. However, as we noted, security data is rarely confined to one location. An EDR log might give you a process ID, but the process's hash and signature information could be in another table. A web log might show a user ID, but that user's permissions and group memberships are stored elsewhere.
To perform meaningful, multi-faceted security analysis, we must be able to connect these disparate pieces of information. This module is dedicated to that task: combining data from multiple tables.
In this first lesson, we will lay the foundational knowledge for joining tables. You will learn to identify the mechanisms that link tables together in a relational database: primary keys and foreign keys. By the end of this lesson, you will be able to examine a database schema—the blueprint of a database—and understand the relationships between its tables. This skill is crucial for both defending systems and, as we'll see later, for conducting database reconnaissance during an attack.
The "Relational" in Relational Databases
Relational databases are designed to minimize data redundancy and enforce data consistency. Instead of storing all information in one massive table, data is logically separated into distinct entities. For example, instead of storing a user's full name and email in every single log entry they generate, we have a users table and a logs table. The users table stores user information once, and the logs table references the appropriate user.
This separation is managed through a system of keys. Let's start by understanding how this structure is visualized.
MySQL in 25 Minutes | Primary and Foreign Keys | Database Terminology for Beginners
This video from 365 Data Science provides a brief but clear introduction to the concept of a relational schema, which is the visual representation of a database's structure.
Please watch the segment from database design to schemas. Focus on the distinction between an ER diagram and a relational schema. We will be focusing on relational schemas, which are straightforward representations of tables, their columns, and the relationships between them.
Primary Keys: The Unique Identifier
Every well-designed table in a relational database has a way to uniquely identify each row. This is the role of the primary key. Think of it as a unique ID number for a record, like a case number for an investigation or a specific event ID in a log.
A primary key has a few strict rules:
- It must be unique. No two rows in the same table can have the same primary key value.
- It cannot be NULL. Every row must have a primary key value.
- It should be stable. A primary key's value should rarely, if ever, change.
The following video offers an excellent explanation of these rules.
Entity Relationship Diagram (ERD) Tutorial - Part 2
This video from Lucid Software clearly explains the concept and rules of a primary key using a practical customer table example.
Watch the segment from primary keys explained. Pay close attention to the three rules the video outlines for an attribute to qualify as a primary key. The example of why a name or address makes a poor primary key is particularly insightful.
In a database schema, the primary key is often indicated with a "PK" marker or by being underlined. For example, in a users table, the user_id column would be the primary key.
It is important not to confuse a primary key with a unique key. While both enforce uniqueness, a table can have only one primary key, but it can have multiple unique keys. Furthermore, unique keys can sometimes permit a single NULL value, whereas primary keys never can.
MySQL in 25 Minutes | Primary and Foreign Keys | Database Terminology for Beginners
Let's return to the 365 Data Science video to solidify the definition of a primary key and see how it's formally distinguished from a unique key.
Watch these two segments: Primary Keys: This section reinforces the properties of primary keys—uniqueness and non-nullability—and shows how they are denoted in a schema. Unique Keys: This part clarifies the difference between primary and unique keys. The example of a "Headquarters Phone Number" is a good illustration of a column that should be unique but isn't the primary identifier.
Foreign Keys: The Bridge Between Tables
If a primary key is a unique ID for a row in its own table, a foreign key is a column in one table that refers to the primary key of another table. This is what creates the relationship, or the "link," between the two tables.
A foreign key enforces referential integrity. This is a critical concept. It means the database guarantees that you cannot add a record to the child table (e.g., Purchase) with a foreign key value (e.g., buyer_id = 999) unless that value already exists as a primary key in the parent table (e.g., Buyers). From a security analysis perspective, this prevents orphaned records and ensures data correlation is reliable.
Let's see this in action.
Entity Relationship Diagram (ERD) Tutorial - Part 2
The Lucid Software video provides another clear, visual explanation, this time for foreign keys.
Please watch the segment from foreign keys explained. Note the key differences highlighted: a foreign key can have duplicate values (one customer can have many orders) and a table can have multiple foreign keys.
Putting It All Together: Reading a Database Schema
Now that you understand the components, let's practice reading a schema. The following resource uses a detective-themed database, which is a fitting analogy for security analysis. It provides a concise visual guide and a clear comparison.
Primary Key vs Foreign Key: The Complete Visual Guide (2026)
This guide from SQLNoir is an excellent, practical walkthrough of keys and schema relationships.
Please read the following sections: Start with the Quick Answer table for a high-level summary. Read the sections What is a Primary Key? and What is a Foreign Key? to review the definitions and see the CREATE TABLE syntax. Finally, focus on the section How Primary and Foreign Keys Work Together. Study the "Detective database schema" diagram carefully. Trace the relationships: how does the interviews table connect to cases and suspects? This is exactly the skill you are learning today.
As you saw in the article, a single table can contain multiple foreign keys, linking it to several other tables. This is common in "fact" or "event" tables that sit at the center of many relationships.
From a security perspective, understanding these relationships is paramount for reconnaissance. If an attacker gains SQL injection on a web page that queries the fact_booksales table, their first goal is to map out the database. By understanding these PK-FK relationships, they can craft queries to pivot from the fact_booksales table to more sensitive data, like store addresses in dim_store_star. As an analyst, you use the same knowledge to correlate data for investigations.
Conclusion
In this lesson, we've unpacked the fundamental mechanism that holds relational databases together. You now understand how to interpret a database schema to identify the keys that define the structure and rules of the data.
Key Takeaways:
- Primary Key (PK): A column (or columns) that provides a unique identifier for every row in a table. It cannot contain
NULLvalues. A table can only have one primary key. - Foreign Key (FK): A column in one table that references the primary key of another table. It acts as a bridge, creating a link and enforcing referential integrity. A table can have multiple foreign keys.
- Database Schema: A visual blueprint of the database that shows tables, columns, and the PK-FK relationships that connect them. Being able to read this "map" is the first step to navigating across tables.
You are now equipped to read the map. In our next lesson, we will learn the SQL command to travel the roads: the INNER JOIN. We will use the key relationships you've learned today to write queries that combine data from multiple tables to build a more complete picture for analysis.