Database Management SystemsKeys

Keys in the Relational Model

LevelBeginner

Duration60 mins

TopicKeys

5 / 5

Foreign Key

The Relational Glue

Everything we've studied so far—superkeys, candidate keys, primary keys, alternate keys—has focused on identification within a single relation. But the true power of the relational model lies in its ability to represent relationships between relations.

Consider a simple business scenario: An order belongs to a customer. In object-oriented terms, we'd embed a Customer object inside an Order object. But relational databases don't embed—they reference. The Order table contains a reference to the Customer table.

This reference mechanism is the foreign key—arguably the most important structural concept in relational database design. Foreign keys transform isolated tables into an interconnected web of data, enabling complex queries, maintaining data consistency, and preventing orphaned records.

What You Will Learn

By the end of this page, you will understand the formal definition of foreign keys and referential integrity, master foreign key implementation including referential actions (CASCADE, SET NULL, etc.), appreciate foreign key design patterns and their implications, and recognize the relationship between foreign keys and relationship cardinality.

Formal Definition of Foreign Key

Definition: Foreign Key

A foreign key is a set of attributes in one relation (the referencing relation) that references the primary key (or a candidate key) of another relation (the referenced relation). The foreign key establishes a link between tuples in the two relations.

Mathematical formalization:

Let R₁(A, B, C, D) and R₂(X, Y, Z) be two relation schemas where {X} is the primary key of R₂.

A foreign key from R₁ to R₂ on attribute B exists if:

B in R₁ and X in R₂ are domain compatible (same or compatible data types)
For every tuple t₁ ∈ r(R₁), either:
- t₁[B] is NULL, or
- There exists a tuple t₂ ∈ r(R₂) such that t₁[B] = t₂[X]

This constraint is called referential integrity.

Terminology:

Term	Definition	Example
Referencing relation (child)	The table containing the foreign key	Order (contains CustomerID)
Referenced relation (parent)	The table being referred to	Customer (has CustomerID as PK)
Foreign key attribute(s)	The column(s) holding the reference	Order.CustomerID
Referenced key	The key being referenced (usually PK)	Customer.CustomerID

Example:

Customer(CustomerID, Name, Email)
         ^^^^^^^^^^^ PK

Order(OrderID, OrderDate, CustomerID, TotalAmount)
      ^^^^^^^ PK         ^^^^^^^^^^^ FK → Customer.CustomerID

The Order.CustomerID foreign key ensures every order is associated with an existing customer.

Key Insight: Foreign Keys Don't Create Uniqueness

Unlike primary and alternate keys, foreign keys do NOT require uniqueness. Many orders can reference the same customer—that's the point! The CustomerID in Order can appear in multiple tuples. Foreign keys enforce existence (referential integrity) not uniqueness.

The Referential Integrity Constraint

Foreign keys exist to enforce referential integrity—one of the fundamental constraints of the relational model alongside entity integrity.

Referential Integrity Rule

If a foreign key exists in a relation, its value must either be NULL (if allowed) or it must match a primary key value in the referenced relation. No foreign key can reference a non-existent primary key value.

What referential integrity prevents:

Orphaned records: An order cannot exist for a non-existent customer
Dangling references: Deleting a customer cannot leave orders pointing to nothing
Invalid relationships: Updating a customer ID cannot break existing order references
Data inconsistency: The database maintains logical coherence across tables

Example: Referential Integrity Violations

Customer Table:
| CustomerID | Name    |
|------------|--------|
| C001       | Alice   |
| C002       | Bob     |

Order Table:
| OrderID | CustomerID | Amount |
|---------|------------|--------|
| O001    | C001       | 150.00 | ← Valid: C001 exists
| O002    | C002       | 75.50  | ← Valid: C002 exists
| O003    | C003       | 200.00 | ← VIOLATION: C003 doesn't exist!

The DBMS will reject the insertion of O003 because CustomerID 'C003' doesn't exist in the Customer table.

Important operations affected:

Operation	Referential Integrity Check	Possible Violation
INSERT into child	Does FK value exist in parent?	Inserting order for non-existent customer
UPDATE child FK	Does new FK value exist in parent?	Changing order to non-existent customer
DELETE from parent	Are there child rows referencing this?	Deleting customer with existing orders
UPDATE parent PK	Are there child rows referencing old value?	Changing customer ID that orders reference

Implementing Foreign Keys in SQL

SQL provides the FOREIGN KEY constraint to establish and enforce referential relationships. Multiple syntax forms exist for different scenarios.

foreign_key_implementation.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- First, create the parent (referenced) table
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(255) UNIQUE NOT NULL
);
 
-- Inline foreign key constraint (single column)
CREATE TABLE Order_V1 (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT REFERENCES Customer(CustomerID),  -- Inline FK
    TotalAmount DECIMAL(10,2) NOT NULL
);
 
-- Table-level foreign key constraint
CREATE TABLE Order_V2 (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL,
    TotalAmount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
 
-- Named foreign key constraint (recommended)
CREATE TABLE Order_V3 (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL,
    TotalAmount DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
);
 
-- Composite foreign key (referencing composite primary key)
CREATE TABLE Course (
    DepartmentCode VARCHAR(10),
    CourseNumber INT,
    CourseName VARCHAR(100) NOT NULL,
    PRIMARY KEY (DepartmentCode, CourseNumber)
);
 
CREATE TABLE Section (
    SectionID INT PRIMARY KEY,
    DepartmentCode VARCHAR(10) NOT NULL,
    CourseNumber INT NOT NULL,
    Semester VARCHAR(20) NOT NULL,
    InstructorID INT NOT NULL,
    CONSTRAINT fk_section_course 
        FOREIGN KEY (DepartmentCode, CourseNumber)
        REFERENCES Course(DepartmentCode, CourseNumber)
);
 
-- Adding FK to existing table
ALTER TABLE Order_V1
ADD CONSTRAINT fk_order_customer 
    FOREIGN KEY (CustomerID) 
    REFERENCES Customer(CustomerID);

Naming Conventions for Foreign Keys

Use descriptive names like 'fk_childtable_parenttable' or 'fk_childtable_column'. Example: 'fk_order_customer' or 'fk_order_customerid'. This makes constraint management and error message interpretation much easier.

Referential Actions (CASCADE, SET NULL, etc.)

When a referenced (parent) row is deleted or updated, the DBMS must decide what happens to the referencing (child) rows. SQL provides referential actions to specify this behavior.

Referential Actions in SQL
Action	ON DELETE Behavior	ON UPDATE Behavior	Use Case
CASCADE	Delete all child rows	Update FK in all child rows	Dependent data that shouldn't exist without parent
SET NULL	Set FK to NULL in child rows	Set FK to NULL in child rows	Optional relationships; child can exist independently
SET DEFAULT	Set FK to default value	Set FK to default value	Rare; when default value is meaningful
RESTRICT	Prevent deletion if children exist	Prevent update if children exist	Strict referential integrity
NO ACTION	Same as RESTRICT (SQL standard)	Same as RESTRICT (SQL standard)	Default in most DBMSs

referential_actions.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- CASCADE: Deleting a customer deletes all their orders
CREATE TABLE Order_Cascade (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
-- DELETE FROM Customer WHERE CustomerID = 1;
-- → All orders for customer 1 are automatically deleted
 
-- SET NULL: Deleting a customer sets orders to "no customer"
CREATE TABLE Order_SetNull (
    OrderID INT PRIMARY KEY,
    CustomerID INT,  -- Must be nullable!
    OrderDate DATE NOT NULL,
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
        ON DELETE SET NULL
        ON UPDATE SET NULL
);
-- DELETE FROM Customer WHERE CustomerID = 1;
-- → All orders for customer 1 now have CustomerID = NULL
 
-- RESTRICT: Cannot delete customer if orders exist
CREATE TABLE Order_Restrict (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);
-- DELETE FROM Customer WHERE CustomerID = 1;
-- → ERROR if customer 1 has any orders
 
-- Mixed actions (common pattern)
CREATE TABLE Order_Mixed (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
        ON DELETE RESTRICT  -- Prevent accidental data loss
        ON UPDATE CASCADE   -- Allow ID corrections to propagate
);

CASCADE with Caution

ON DELETE CASCADE can cause massive unintended data loss. A single DELETE statement could cascade through multiple tables, removing thousands of rows. Use CASCADE only when the business logic genuinely requires it (e.g., order line items when an order is deleted). For important data, prefer RESTRICT and require explicit deletion of child rows.

Self-Referencing Foreign Keys

A special but common case occurs when a table references itself—the foreign key points to the primary key of the same table. This models hierarchical or recursive relationships.

Definition: Self-Referencing Foreign Key

A self-referencing (or recursive) foreign key is a foreign key that references the primary key of the same table. This creates a parent-child relationship within a single table, commonly used for hierarchies like organizational structures, categories, or bill-of-materials.

self_referencing_foreign_key.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- Employee hierarchy (manager-subordinate relationship)
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Title VARCHAR(100) NOT NULL,
    ManagerID INT,  -- Self-referencing FK (nullable for top-level)
    CONSTRAINT fk_employee_manager 
        FOREIGN KEY (ManagerID) 
        REFERENCES Employee(EmployeeID)
        ON DELETE SET NULL  -- If manager leaves, subordinates remain
);
 
-- Sample data showing hierarchy:
INSERT INTO Employee (EmployeeID, Name, Title, ManagerID) VALUES
(1, 'Alice Chen', 'CEO', NULL),           -- Top level, no manager
(2, 'Bob Smith', 'VP Engineering', 1),     -- Reports to Alice
(3, 'Carol Jones', 'VP Sales', 1),         -- Reports to Alice
(4, 'David Brown', 'Senior Engineer', 2),  -- Reports to Bob
(5, 'Eve Wilson', 'Engineer', 2),          -- Reports to Bob
(6, 'Frank Lee', 'Sales Manager', 3);      -- Reports to Carol
 
-- Query: Find all employees with their manager names
SELECT 
    e.Name AS Employee,
    e.Title,
    m.Name AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.ManagerID = m.EmployeeID;
 
-- Category hierarchy (e.g., product categories)
CREATE TABLE Category (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(100) NOT NULL,
    ParentCategoryID INT,
    CONSTRAINT fk_category_parent 
        FOREIGN KEY (ParentCategoryID) 
        REFERENCES Category(CategoryID)
        ON DELETE CASCADE  -- Deleting parent deletes subcategories
);
 
-- Bill of Materials (BOM) - recursive assembly structure
CREATE TABLE Component (
    ComponentID INT PRIMARY KEY,
    ComponentName VARCHAR(100) NOT NULL,
    UnitCost DECIMAL(10,2) NOT NULL
);
 
CREATE TABLE Assembly (
    ParentComponentID INT,
    ChildComponentID INT,
    Quantity INT NOT NULL,
    PRIMARY KEY (ParentComponentID, ChildComponentID),
    FOREIGN KEY (ParentComponentID) REFERENCES Component(ComponentID),
    FOREIGN KEY (ChildComponentID) REFERENCES Component(ComponentID),
    -- Prevent circular references with CHECK (basic)
    CHECK (ParentComponentID != ChildComponentID)
);

Common Self-Referencing Patterns

•Organizational charts: Employee → ManagerID → Employee
•Category trees: Category → ParentCategoryID → Category
•Folder structures: Folder → ParentFolderID → Folder
•Comment threads: Comment → ReplyToCommentID → Comment
•Bill of materials: Component → ContainsComponentID → Component
•Menu systems: MenuItem → ParentMenuItemID → MenuItem

Foreign Keys and Relationship Cardinality

Foreign keys are the physical implementation of relationships between entities. The placement and constraints of foreign keys directly correspond to relationship cardinality (1:1, 1:N, M:N).

Converting Mermaid diagram...

Implementing each cardinality:

cardinality_implementation.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- ===================================================
-- ONE-TO-MANY (1:N): Customer has many Orders
-- FK on the "many" side references the "one" side
-- ===================================================
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);
 
CREATE TABLE CustomerOrder (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL,  -- FK on the "many" side
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
-- One customer → many orders
-- Each order → exactly one customer
 
-- ===================================================
-- ONE-TO-ONE (1:1): Employee has one ParkingSpot
-- FK with UNIQUE constraint creates 1:1
-- ===================================================
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);
 
CREATE TABLE ParkingSpot (
    SpotID INT PRIMARY KEY,
    Location VARCHAR(50) NOT NULL,
    EmployeeID INT UNIQUE,  -- UNIQUE makes it 1:1
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);
-- One employee → at most one parking spot
-- One parking spot → at most one employee
 
-- Alternative 1:1 - FK in either table works
CREATE TABLE EmployeeWithParking (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    ParkingSpotID INT UNIQUE,  -- FK with UNIQUE
    FOREIGN KEY (ParkingSpotID) REFERENCES ParkingSpot(SpotID)
);
 
-- ===================================================
-- MANY-TO-MANY (M:N): Students enroll in Courses
-- Junction table with two FKs, composite PK
-- ===================================================
CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);
 
CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL
);
 
CREATE TABLE Enrollment (
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    EnrollmentDate DATE NOT NULL,
    Grade CHAR(2),
    PRIMARY KEY (StudentID, CourseID),  -- Composite PK
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
-- One student → many courses (via Enrollment)
-- One course → many students (via Enrollment)

Cardinality to Foreign Key Mapping
Cardinality	FK Placement	Additional Constraint	Example
One-to-Many (1:N)	FK on N-side	None needed	Order.CustomerID → Customer
One-to-One (1:1)	FK on either side	UNIQUE on FK column	ParkingSpot.EmployeeID UNIQUE
Many-to-Many (M:N)	Junction table with 2 FKs	Composite PK or UNIQUE	Enrollment(StudentID, CourseID)

Foreign Key Best Practices

Effective foreign key design requires balancing data integrity, performance, and practical maintainability. Here are key guidelines from experienced database practitioners.

Foreign Key Best Practices

•Always name your constraints: Use descriptive names like 'fk_order_customer'. This makes error messages interpretable and constraint management (dropping, altering) straightforward.
•Index foreign key columns: Most DBMSs don't automatically index FK columns. Add explicit indexes to improve JOIN performance and DELETE/UPDATE checks on parent tables.
•Choose referential actions deliberately: Don't accept defaults blindly. Consider what should happen when parent rows are deleted—CASCADE, SET NULL, or RESTRICT?
•Prefer RESTRICT for critical data: ON DELETE CASCADE is convenient but dangerous. For important data (customers, employees), use RESTRICT and handle deletions explicitly.
•Use SET NULL for optional relationships: If the relationship is truly optional and child rows can exist without parents, SET NULL on DELETE/UPDATE is appropriate.
•Consider performance impact: Every DML operation checks FK constraints. In high-throughput systems, FKs can be a bottleneck. Some systems validate at application layer instead.
•Document when omitting FKs: If you choose not to enforce FKs at database level (for performance), document this decision and ensure application-level enforcement.

foreign_key_indexing.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Foreign key columns should be indexed for performance
-- Most DBMSs don't auto-create these indexes!
 
CREATE TABLE Order_Optimized (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    ProductID INT NOT NULL,
    OrderDate DATE NOT NULL,
    Quantity INT NOT NULL,
    
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
        ON DELETE RESTRICT,
        
    CONSTRAINT fk_order_product
        FOREIGN KEY (ProductID)
        REFERENCES Product(ProductID)
        ON DELETE RESTRICT
);
 
-- Explicitly create indexes on FK columns
CREATE INDEX idx_order_customer ON Order_Optimized(CustomerID);
CREATE INDEX idx_order_product ON Order_Optimized(ProductID);
 
-- Why index FK columns?
-- 1. JOIN performance: SELECT * FROM Order JOIN Customer ON ...
-- 2. DELETE validation: DELETE FROM Customer WHERE CustomerID = X
--    → DBMS must check if any Orders reference this customer
--    → Without index: full table scan on Order table
-- 3. UPDATE validation: Same as DELETE when updating PK

Performance vs Integrity Trade-off

Some high-performance systems (OLTP workloads, data warehouses) omit foreign key constraints at the database level for performance. This shifts integrity enforcement to the application layer. This is a valid trade-off in specific scenarios, but increases risk of data corruption if application bugs exist. Make this choice consciously and document it.

Summary: Foreign Key Mastery

We've comprehensively covered foreign keys—the mechanism that connects relations and enforces referential integrity. This completes our exploration of keys in the relational model. Let's consolidate the key insights:

Key Takeaways

•Foreign keys establish inter-table relationships — They reference primary (or candidate) keys in other tables, creating the 'relational' aspect of relational databases.
•Referential integrity ensures consistency — Foreign key values must match existing primary key values (or be NULL if allowed), preventing orphaned records.
•Referential actions control cascading behavior — CASCADE, SET NULL, RESTRICT, and NO ACTION determine what happens when parent rows change.
•Self-referencing keys model hierarchies — Tables can reference themselves for organizational structures, categories, and recursive relationships.
•FK placement corresponds to cardinality — 1:N puts FK on N-side; 1:1 adds UNIQUE to FK; M:N uses junction tables with two FKs.
•Best practices balance integrity and performance — Name constraints, index FK columns, choose referential actions deliberately, and document trade-offs.

Module Complete: Keys in the Relational Model

You've now mastered all five key types:

Superkey — Any set of attributes that uniquely identifies tuples
Candidate Key — Minimal superkey (irreducible unique identifier)
Primary Key — Designated candidate key for the relation
Alternate Key — Non-primary candidate keys with uniqueness constraints
Foreign Key — Inter-table reference enforcing referential integrity

These concepts form the structural backbone of relational database design. Every table you create, every relationship you model, and every query you write builds upon this foundation.

Module Complete

You have completed the Keys module. You can now identify and implement all key types in relational schemas, understand the hierarchy from superkeys to foreign keys, apply proper constraints (UNIQUE, NOT NULL, FOREIGN KEY) for each key type, design relationships using appropriate foreign key patterns, and make informed decisions about referential actions and performance trade-offs. Next, explore integrity constraints to see how these key concepts integrate into the broader data integrity framework.

5 / 5

Loading learning content...

Database Management SystemsKeys

Keys in the Relational Model

LevelBeginner

Duration60 mins

TopicKeys

5 / 5

Foreign Key

The Relational Glue

What You Will Learn

Formal Definition of Foreign Key

Definition: Foreign Key

Mathematical formalization:

Let R₁(A, B, C, D) and R₂(X, Y, Z) be two relation schemas where {X} is the primary key of R₂.

A foreign key from R₁ to R₂ on attribute B exists if:

B in R₁ and X in R₂ are domain compatible (same or compatible data types)
For every tuple t₁ ∈ r(R₁), either:
- t₁[B] is NULL, or
- There exists a tuple t₂ ∈ r(R₂) such that t₁[B] = t₂[X]

This constraint is called referential integrity.

Terminology:

Term	Definition	Example
Referencing relation (child)	The table containing the foreign key	Order (contains CustomerID)
Referenced relation (parent)	The table being referred to	Customer (has CustomerID as PK)
Foreign key attribute(s)	The column(s) holding the reference	Order.CustomerID
Referenced key	The key being referenced (usually PK)	Customer.CustomerID

Example:

Customer(CustomerID, Name, Email)
         ^^^^^^^^^^^ PK

Order(OrderID, OrderDate, CustomerID, TotalAmount)
      ^^^^^^^ PK         ^^^^^^^^^^^ FK → Customer.CustomerID

The Order.CustomerID foreign key ensures every order is associated with an existing customer.

Key Insight: Foreign Keys Don't Create Uniqueness

The Referential Integrity Constraint

Foreign keys exist to enforce referential integrity—one of the fundamental constraints of the relational model alongside entity integrity.

Referential Integrity Rule

What referential integrity prevents:

Orphaned records: An order cannot exist for a non-existent customer
Dangling references: Deleting a customer cannot leave orders pointing to nothing
Invalid relationships: Updating a customer ID cannot break existing order references
Data inconsistency: The database maintains logical coherence across tables

Example: Referential Integrity Violations

Customer Table:
| CustomerID | Name    |
|------------|--------|
| C001       | Alice   |
| C002       | Bob     |

Order Table:
| OrderID | CustomerID | Amount |
|---------|------------|--------|
| O001    | C001       | 150.00 | ← Valid: C001 exists
| O002    | C002       | 75.50  | ← Valid: C002 exists
| O003    | C003       | 200.00 | ← VIOLATION: C003 doesn't exist!

The DBMS will reject the insertion of O003 because CustomerID 'C003' doesn't exist in the Customer table.

Important operations affected:

Operation	Referential Integrity Check	Possible Violation
INSERT into child	Does FK value exist in parent?	Inserting order for non-existent customer
UPDATE child FK	Does new FK value exist in parent?	Changing order to non-existent customer
DELETE from parent	Are there child rows referencing this?	Deleting customer with existing orders
UPDATE parent PK	Are there child rows referencing old value?	Changing customer ID that orders reference

Implementing Foreign Keys in SQL

SQL provides the FOREIGN KEY constraint to establish and enforce referential relationships. Multiple syntax forms exist for different scenarios.

foreign_key_implementation.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- First, create the parent (referenced) table
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(255) UNIQUE NOT NULL
);
 
-- Inline foreign key constraint (single column)
CREATE TABLE Order_V1 (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT REFERENCES Customer(CustomerID),  -- Inline FK
    TotalAmount DECIMAL(10,2) NOT NULL
);
 
-- Table-level foreign key constraint
CREATE TABLE Order_V2 (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL,
    TotalAmount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
 
-- Named foreign key constraint (recommended)
CREATE TABLE Order_V3 (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL,
    TotalAmount DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
);
 
-- Composite foreign key (referencing composite primary key)
CREATE TABLE Course (
    DepartmentCode VARCHAR(10),
    CourseNumber INT,
    CourseName VARCHAR(100) NOT NULL,
    PRIMARY KEY (DepartmentCode, CourseNumber)
);
 
CREATE TABLE Section (
    SectionID INT PRIMARY KEY,
    DepartmentCode VARCHAR(10) NOT NULL,
    CourseNumber INT NOT NULL,
    Semester VARCHAR(20) NOT NULL,
    InstructorID INT NOT NULL,
    CONSTRAINT fk_section_course 
        FOREIGN KEY (DepartmentCode, CourseNumber)
        REFERENCES Course(DepartmentCode, CourseNumber)
);
 
-- Adding FK to existing table
ALTER TABLE Order_V1
ADD CONSTRAINT fk_order_customer 
    FOREIGN KEY (CustomerID) 
    REFERENCES Customer(CustomerID);

Naming Conventions for Foreign Keys

Referential Actions (CASCADE, SET NULL, etc.)

When a referenced (parent) row is deleted or updated, the DBMS must decide what happens to the referencing (child) rows. SQL provides referential actions to specify this behavior.

Referential Actions in SQL
Action	ON DELETE Behavior	ON UPDATE Behavior	Use Case
CASCADE	Delete all child rows	Update FK in all child rows	Dependent data that shouldn't exist without parent
SET NULL	Set FK to NULL in child rows	Set FK to NULL in child rows	Optional relationships; child can exist independently
SET DEFAULT	Set FK to default value	Set FK to default value	Rare; when default value is meaningful
RESTRICT	Prevent deletion if children exist	Prevent update if children exist	Strict referential integrity
NO ACTION	Same as RESTRICT (SQL standard)	Same as RESTRICT (SQL standard)	Default in most DBMSs

referential_actions.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- CASCADE: Deleting a customer deletes all their orders
CREATE TABLE Order_Cascade (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
-- DELETE FROM Customer WHERE CustomerID = 1;
-- → All orders for customer 1 are automatically deleted
 
-- SET NULL: Deleting a customer sets orders to "no customer"
CREATE TABLE Order_SetNull (
    OrderID INT PRIMARY KEY,
    CustomerID INT,  -- Must be nullable!
    OrderDate DATE NOT NULL,
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
        ON DELETE SET NULL
        ON UPDATE SET NULL
);
-- DELETE FROM Customer WHERE CustomerID = 1;
-- → All orders for customer 1 now have CustomerID = NULL
 
-- RESTRICT: Cannot delete customer if orders exist
CREATE TABLE Order_Restrict (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);
-- DELETE FROM Customer WHERE CustomerID = 1;
-- → ERROR if customer 1 has any orders
 
-- Mixed actions (common pattern)
CREATE TABLE Order_Mixed (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
        ON DELETE RESTRICT  -- Prevent accidental data loss
        ON UPDATE CASCADE   -- Allow ID corrections to propagate
);

CASCADE with Caution

Self-Referencing Foreign Keys

A special but common case occurs when a table references itself—the foreign key points to the primary key of the same table. This models hierarchical or recursive relationships.

Definition: Self-Referencing Foreign Key

self_referencing_foreign_key.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- Employee hierarchy (manager-subordinate relationship)
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Title VARCHAR(100) NOT NULL,
    ManagerID INT,  -- Self-referencing FK (nullable for top-level)
    CONSTRAINT fk_employee_manager 
        FOREIGN KEY (ManagerID) 
        REFERENCES Employee(EmployeeID)
        ON DELETE SET NULL  -- If manager leaves, subordinates remain
);
 
-- Sample data showing hierarchy:
INSERT INTO Employee (EmployeeID, Name, Title, ManagerID) VALUES
(1, 'Alice Chen', 'CEO', NULL),           -- Top level, no manager
(2, 'Bob Smith', 'VP Engineering', 1),     -- Reports to Alice
(3, 'Carol Jones', 'VP Sales', 1),         -- Reports to Alice
(4, 'David Brown', 'Senior Engineer', 2),  -- Reports to Bob
(5, 'Eve Wilson', 'Engineer', 2),          -- Reports to Bob
(6, 'Frank Lee', 'Sales Manager', 3);      -- Reports to Carol
 
-- Query: Find all employees with their manager names
SELECT 
    e.Name AS Employee,
    e.Title,
    m.Name AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.ManagerID = m.EmployeeID;
 
-- Category hierarchy (e.g., product categories)
CREATE TABLE Category (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(100) NOT NULL,
    ParentCategoryID INT,
    CONSTRAINT fk_category_parent 
        FOREIGN KEY (ParentCategoryID) 
        REFERENCES Category(CategoryID)
        ON DELETE CASCADE  -- Deleting parent deletes subcategories
);
 
-- Bill of Materials (BOM) - recursive assembly structure
CREATE TABLE Component (
    ComponentID INT PRIMARY KEY,
    ComponentName VARCHAR(100) NOT NULL,
    UnitCost DECIMAL(10,2) NOT NULL
);
 
CREATE TABLE Assembly (
    ParentComponentID INT,
    ChildComponentID INT,
    Quantity INT NOT NULL,
    PRIMARY KEY (ParentComponentID, ChildComponentID),
    FOREIGN KEY (ParentComponentID) REFERENCES Component(ComponentID),
    FOREIGN KEY (ChildComponentID) REFERENCES Component(ComponentID),
    -- Prevent circular references with CHECK (basic)
    CHECK (ParentComponentID != ChildComponentID)
);

Common Self-Referencing Patterns

•Organizational charts: Employee → ManagerID → Employee
•Category trees: Category → ParentCategoryID → Category
•Folder structures: Folder → ParentFolderID → Folder
•Comment threads: Comment → ReplyToCommentID → Comment
•Bill of materials: Component → ContainsComponentID → Component
•Menu systems: MenuItem → ParentMenuItemID → MenuItem

Foreign Keys and Relationship Cardinality

Foreign keys are the physical implementation of relationships between entities. The placement and constraints of foreign keys directly correspond to relationship cardinality (1:1, 1:N, M:N).

Converting Mermaid diagram...

Implementing each cardinality:

cardinality_implementation.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- ===================================================
-- ONE-TO-MANY (1:N): Customer has many Orders
-- FK on the "many" side references the "one" side
-- ===================================================
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);
 
CREATE TABLE CustomerOrder (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL,  -- FK on the "many" side
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
-- One customer → many orders
-- Each order → exactly one customer
 
-- ===================================================
-- ONE-TO-ONE (1:1): Employee has one ParkingSpot
-- FK with UNIQUE constraint creates 1:1
-- ===================================================
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);
 
CREATE TABLE ParkingSpot (
    SpotID INT PRIMARY KEY,
    Location VARCHAR(50) NOT NULL,
    EmployeeID INT UNIQUE,  -- UNIQUE makes it 1:1
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);
-- One employee → at most one parking spot
-- One parking spot → at most one employee
 
-- Alternative 1:1 - FK in either table works
CREATE TABLE EmployeeWithParking (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    ParkingSpotID INT UNIQUE,  -- FK with UNIQUE
    FOREIGN KEY (ParkingSpotID) REFERENCES ParkingSpot(SpotID)
);
 
-- ===================================================
-- MANY-TO-MANY (M:N): Students enroll in Courses
-- Junction table with two FKs, composite PK
-- ===================================================
CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);
 
CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL
);
 
CREATE TABLE Enrollment (
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    EnrollmentDate DATE NOT NULL,
    Grade CHAR(2),
    PRIMARY KEY (StudentID, CourseID),  -- Composite PK
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
-- One student → many courses (via Enrollment)
-- One course → many students (via Enrollment)

Cardinality to Foreign Key Mapping
Cardinality	FK Placement	Additional Constraint	Example
One-to-Many (1:N)	FK on N-side	None needed	Order.CustomerID → Customer
One-to-One (1:1)	FK on either side	UNIQUE on FK column	ParkingSpot.EmployeeID UNIQUE
Many-to-Many (M:N)	Junction table with 2 FKs	Composite PK or UNIQUE	Enrollment(StudentID, CourseID)

Foreign Key Best Practices

Effective foreign key design requires balancing data integrity, performance, and practical maintainability. Here are key guidelines from experienced database practitioners.

Foreign Key Best Practices

•Always name your constraints: Use descriptive names like 'fk_order_customer'. This makes error messages interpretable and constraint management (dropping, altering) straightforward.
•Index foreign key columns: Most DBMSs don't automatically index FK columns. Add explicit indexes to improve JOIN performance and DELETE/UPDATE checks on parent tables.
•Choose referential actions deliberately: Don't accept defaults blindly. Consider what should happen when parent rows are deleted—CASCADE, SET NULL, or RESTRICT?
•Prefer RESTRICT for critical data: ON DELETE CASCADE is convenient but dangerous. For important data (customers, employees), use RESTRICT and handle deletions explicitly.
•Use SET NULL for optional relationships: If the relationship is truly optional and child rows can exist without parents, SET NULL on DELETE/UPDATE is appropriate.
•Consider performance impact: Every DML operation checks FK constraints. In high-throughput systems, FKs can be a bottleneck. Some systems validate at application layer instead.
•Document when omitting FKs: If you choose not to enforce FKs at database level (for performance), document this decision and ensure application-level enforcement.

foreign_key_indexing.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Foreign key columns should be indexed for performance
-- Most DBMSs don't auto-create these indexes!
 
CREATE TABLE Order_Optimized (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    ProductID INT NOT NULL,
    OrderDate DATE NOT NULL,
    Quantity INT NOT NULL,
    
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID)
        ON DELETE RESTRICT,
        
    CONSTRAINT fk_order_product
        FOREIGN KEY (ProductID)
        REFERENCES Product(ProductID)
        ON DELETE RESTRICT
);
 
-- Explicitly create indexes on FK columns
CREATE INDEX idx_order_customer ON Order_Optimized(CustomerID);
CREATE INDEX idx_order_product ON Order_Optimized(ProductID);
 
-- Why index FK columns?
-- 1. JOIN performance: SELECT * FROM Order JOIN Customer ON ...
-- 2. DELETE validation: DELETE FROM Customer WHERE CustomerID = X
--    → DBMS must check if any Orders reference this customer
--    → Without index: full table scan on Order table
-- 3. UPDATE validation: Same as DELETE when updating PK

Performance vs Integrity Trade-off

Summary: Foreign Key Mastery

Key Takeaways

•Foreign keys establish inter-table relationships — They reference primary (or candidate) keys in other tables, creating the 'relational' aspect of relational databases.
•Referential integrity ensures consistency — Foreign key values must match existing primary key values (or be NULL if allowed), preventing orphaned records.
•Referential actions control cascading behavior — CASCADE, SET NULL, RESTRICT, and NO ACTION determine what happens when parent rows change.
•Self-referencing keys model hierarchies — Tables can reference themselves for organizational structures, categories, and recursive relationships.
•FK placement corresponds to cardinality — 1:N puts FK on N-side; 1:1 adds UNIQUE to FK; M:N uses junction tables with two FKs.
•Best practices balance integrity and performance — Name constraints, index FK columns, choose referential actions deliberately, and document trade-offs.

Module Complete: Keys in the Relational Model

You've now mastered all five key types:

Superkey — Any set of attributes that uniquely identifies tuples
Candidate Key — Minimal superkey (irreducible unique identifier)
Primary Key — Designated candidate key for the relation
Alternate Key — Non-primary candidate keys with uniqueness constraints
Foreign Key — Inter-table reference enforcing referential integrity

These concepts form the structural backbone of relational database design. Every table you create, every relationship you model, and every query you write builds upon this foundation.

Module Complete

5 / 5