Table of Contents
- Introduction
- What is a Foreign Key?
- Importance of Foreign Keys
- Primary Key vs Foreign Key
- Syntax for Declaring a Foreign Key
- Creating Foreign Keys During Table Creation
- Adding Foreign Keys to Existing Tables
- One-to-Many Relationship
- One-to-One Relationship
- Many-to-Many Relationship (Using Junction Tables)
- ON DELETE and ON UPDATE Options
- CASCADE, SET NULL, RESTRICT Explained
- Foreign Keys Across Multiple Columns
- Naming Foreign Keys for Clarity
- Foreign Key Constraints and Data Integrity
- Viewing Foreign Key Relationships
- Dropping Foreign Keys
- Real-World Example: Orders and Customers
- Common Errors and Troubleshooting
- Summary and What’s Next
1. Introduction
In relational databases, foreign keys establish connections between tables. They enforce rules about how data is shared and related across tables, enabling consistency, normalization, and real-world modeling.
2. What is a Foreign Key?
A foreign key is a field (or group of fields) in one table that refers to the primary key in another table. It creates a link between the two tables.
3. Importance of Foreign Keys
- Maintain referential integrity
- Prevent orphaned records
- Represent real-world relationships (e.g., orders belong to customers)
- Help with joins and query performance when indexed
4. Primary Key vs Foreign Key
| Feature | Primary Key | Foreign Key |
|---|---|---|
| Definition | Unique identifier in a table | Points to a primary key in another table |
| Must be unique | Yes | No |
| Can be NULL? | No | Yes (depending on use case) |
| One per table? | Only one (composite allowed) | Can have multiple |
5. Syntax for Declaring a Foreign Key
sqlCopyEditFOREIGN KEY (column_name) REFERENCES parent_table(primary_key)
6. Creating Foreign Keys During Table Creation
sqlCopyEditCREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
7. Adding Foreign Keys to Existing Tables
sqlCopyEditALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
Name the constraint for easier debugging and management.
8. One-to-Many Relationship
One customer can place many orders
sqlCopyEdit-- customers table
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100)
);
-- orders table
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
9. One-to-One Relationship
When one row in table A corresponds to exactly one row in table B:
sqlCopyEditCREATE TABLE user_profiles (
user_id INT PRIMARY KEY,
bio TEXT,
FOREIGN KEY (user_id) REFERENCES users(id)
);
Here, user_id is both primary and foreign key.
10. Many-to-Many Relationship (Using Junction Tables)
Use a third table with two foreign keys:
sqlCopyEditCREATE TABLE student_courses (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
11. ON DELETE and ON UPDATE Options
Foreign keys can define what happens when the referenced data is deleted or updated:
sqlCopyEditFOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE;
12. CASCADE, SET NULL, RESTRICT Explained
| Option | Behavior |
|---|---|
CASCADE | Delete/update child rows automatically |
SET NULL | Set foreign key field to NULL if parent is deleted |
RESTRICT | Prevent deletion/update if foreign key exists |
NO ACTION | Similar to RESTRICT (DB-dependent) |
13. Foreign Keys Across Multiple Columns
Composite keys:
sqlCopyEditFOREIGN KEY (product_id, supplier_id)
REFERENCES inventory(product_id, supplier_id);
Ensure both columns exist and are indexed together in the referenced table.
14. Naming Foreign Keys for Clarity
sqlCopyEditCONSTRAINT fk_order_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id)
A clear constraint name simplifies debugging and schema reviews.
15. Foreign Key Constraints and Data Integrity
- Prevents inserting invalid values
- Prevents deleting referenced values
- Helps ensure relationships are always valid
16. Viewing Foreign Key Relationships
In MySQL:
sqlCopyEditSHOW CREATE TABLE orders;
In PostgreSQL:
sqlCopyEditSELECT
conname, conrelid::regclass AS table_name
FROM pg_constraint
WHERE contype = 'f';
17. Dropping Foreign Keys
sqlCopyEditALTER TABLE orders
DROP FOREIGN KEY fk_customer; -- MySQL
ALTER TABLE orders
DROP CONSTRAINT fk_customer; -- PostgreSQL
18. Real-World Example: Orders and Customers
sqlCopyEditCREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
order_total DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
This ensures every order is linked to a valid customer.
19. Common Errors and Troubleshooting
| Error Message | Likely Cause |
|---|---|
| Cannot add foreign key constraint | Data mismatch in column type or length |
| Cannot delete or update parent row | RESTRICT or missing CASCADE on delete |
| Foreign key constraint fails | Inserting a value that doesn’t exist in parent |
20. Summary and What’s Next
Foreign keys are essential for establishing relational integrity in SQL databases. They enforce consistency, clarify relationships, and power robust query design. Whether you’re creating one-to-one or many-to-many links, understanding foreign keys is key to designing proper relational schemas.