Primary Keys, Foreign Keys, and Relationships in Azure Relational Databases (DP-900)
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 PracticeConsider 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 IntegrityReferential 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 KeysSometimes 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 RelationshipsTip 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.
