Primary Keys, Foreign Keys, and Relationships

Why Are Primary Keys, Foreign Keys, and Relationships Important?

Understanding primary keys, foreign keys, and relationships is foundational to working with relational databases — and it is a core topic on the Microsoft DP-900: Azure Data Fundamentals exam. These concepts ensure data integrity, consistency, and efficient organization across tables. Without them, relational databases would be unable to reliably link data together, leading to duplicate records, orphaned rows, and unreliable query results.

In real-world Azure services like Azure SQL Database, Azure Database for MySQL, and Azure Database for PostgreSQL, these concepts are applied constantly to model business data correctly.

---

What Is a Primary Key?

A primary key is a column (or combination of columns) in a table that uniquely identifies each row. Every table in a well-designed relational database should have a primary key.

Key characteristics of a primary key:
- Uniqueness: No two rows can have the same primary key value.
- Not Null: A primary key column cannot contain NULL values.
- Single per table: Each table can have only one primary key (though it can span multiple columns as a composite key).
- Immutable (best practice): Primary key values should ideally not change over time.

Example: In a Customers table, a column called CustomerID (e.g., 1, 2, 3…) serves as the primary key. Each customer has a unique ID that distinguishes them from every other customer.

---

What Is a Foreign Key?

A foreign key is a column (or set of columns) in one table that references the primary key of another table. It creates a link between two tables, establishing a relationship.

Key characteristics of a foreign key:
- It enforces referential integrity — meaning you cannot insert a value into the foreign key column unless that value already exists in the referenced primary key column.
- A table can have multiple foreign keys, each referencing different tables.
- Foreign key columns can contain NULL values (unless explicitly restricted), which means the relationship is optional.
- Foreign key values can be duplicated (unlike primary keys).

Example: In an Orders table, a column called CustomerID is a foreign key that references the CustomerID primary key in the Customers table. This tells us which customer placed each order.

---

What Are Relationships?

Relationships describe how tables are connected to each other through primary and foreign keys. There are three main types:

1. One-to-Many (1:M)
This is the most common type. One record in Table A can be associated with many records in Table B, but each record in Table B links to only one record in Table A.
Example: One customer can place many orders. The Customers table (one side) relates to the Orders table (many side) via CustomerID.

2. One-to-One (1:1)
One record in Table A is associated with exactly one record in Table B, and vice versa. This is less common and often used to split a table for security or performance reasons.
Example: One employee has one employee badge record.

3. Many-to-Many (M:M)
Many records in Table A can be associated with many records in Table B. This requires a junction table (also called a bridge table or linking table) that contains foreign keys referencing both tables.
Example: Students and Courses — a student can enroll in many courses, and a course can have many students. A junction table called Enrollments would contain StudentID (FK) and CourseID (FK).

---

How It Works in Practice

Consider a simple database with two tables:

Customers Table:
| CustomerID (PK) | Name | Email |
| 1 | Alice | alice@email.com |
| 2 | Bob | bob@email.com |

Orders Table:
| OrderID (PK) | OrderDate | CustomerID (FK) |
| 101 | 2024-01-15 | 1 |
| 102 | 2024-01-16 | 1 |
| 103 | 2024-01-17 | 2 |

Here, CustomerID in the Orders table is a foreign key that references CustomerID in the Customers table. This enforces that:
- You cannot create an order for CustomerID 99 if no such customer exists (referential integrity).
- If you try to delete Customer 1 while orders exist for them, the database will either prevent the deletion or cascade the delete depending on the constraint configuration.

This is a one-to-many relationship: one customer has many orders.

---

Referential Integrity

Referential integrity is the guarantee that relationships between tables remain consistent. It is enforced by foreign key constraints. Specifically:
- You cannot add a row to a child table with a foreign key value that does not exist in the parent table's primary key.
- You cannot delete a row from a parent table if dependent rows exist in the child table (unless cascade rules are defined).
- You cannot update a primary key value in the parent table if it would break the link with child rows.

This concept is frequently tested on the DP-900 exam.

---

Composite Keys

Sometimes a single column is not enough to uniquely identify a row. In such cases, a composite primary key uses two or more columns together. For example, in the Enrollments junction table, the combination of StudentID + CourseID forms the composite primary key, because neither alone is unique.

---

Exam Tips: Answering Questions on Primary Keys, Foreign Keys, and Relationships

Tip 1: Know the definitions cold.
The DP-900 exam frequently tests basic definitions. Remember: a primary key uniquely identifies a row, and a foreign key references a primary key in another table. If a question asks what ensures uniqueness, the answer is primary key.

Tip 2: Primary key = unique + not null.
If a question describes a column that allows NULL values, it is not a primary key. Primary keys never allow NULLs. This is a common distractor in exam questions.

Tip 3: Foreign keys enforce referential integrity.
Whenever you see the term referential integrity in a question, think foreign keys. The exam loves to test whether you know that foreign keys are the mechanism for enforcing relationships between tables.

Tip 4: Understand relationship types.
Be ready to identify whether a scenario describes a one-to-one, one-to-many, or many-to-many relationship. The biggest giveaway for many-to-many is the need for a junction table. If the question mentions a third table linking two others, it is many-to-many.

Tip 5: One-to-many is the most common relationship.
If you are unsure and the question describes a standard business scenario (customers and orders, departments and employees), it is almost always one-to-many.

Tip 6: A table can have only ONE primary key but MULTIPLE foreign keys.
This is a frequently tested distinction. Don't confuse the two. A composite key (multiple columns) still counts as one primary key.

Tip 7: Watch for the word 'normalize.'
Normalization involves splitting data into multiple related tables connected by primary and foreign keys to reduce redundancy. If a question asks about reducing data duplication, think normalization and relationships.

Tip 8: Junction tables resolve many-to-many relationships.
If a question asks how to implement a many-to-many relationship, the correct answer always involves creating a third (junction/bridge) table with foreign keys to both original tables.

Tip 9: Foreign key values CAN repeat.
Unlike primary keys, foreign key columns can have duplicate values. In the Orders table example, CustomerID 1 appears in multiple rows — this is perfectly valid and expected in a one-to-many relationship.

Tip 10: Read scenarios carefully for key indicators.
Exam questions often present a scenario and ask you to identify which column should be the primary key or foreign key. Look for the column that is unique to each row for primary keys, and the column that matches a primary key in another table for foreign keys.

Tip 11: Azure-specific context.
Remember that these concepts apply across all Azure relational database services (Azure SQL Database, Azure SQL Managed Instance, Azure Database for MySQL, Azure Database for PostgreSQL). The DP-900 may frame questions in the context of these services, but the underlying relational concepts remain the same.

---

Quick Summary for Exam Day

- Primary Key: Uniquely identifies each row. One per table. No NULLs. No duplicates.
- Foreign Key: References a primary key in another table. Enforces referential integrity. Can have NULLs and duplicates. Multiple allowed per table.
- One-to-Many: Most common. One parent row links to many child rows.
- One-to-One: One row in each table maps to exactly one row in the other.
- Many-to-Many: Requires a junction table with two foreign keys.
- Referential Integrity: Ensured by foreign key constraints — prevents orphaned records and broken links between tables.