What is a Foreign Key and How Does It Enforce Referential Integrity?
💡 Concept Name
Foreign Key – A database constraint that creates a link between two tables by referencing the primary key of another table.
📘 Quick Intro
A foreign key enforces referential integrity by ensuring that the value in one table matches a valid entry in another table, preventing orphan records and maintaining data consistency.
🧠 Analogy / Short Story
Think of a foreign key like a passport number linking a traveler’s details to their country’s database. If the passport number doesn’t exist in the country’s system, the traveler cannot be registered. Similarly, foreign keys ensure related data exists in the parent table before allowing references.
🔧 Technical Explanation
- 🔗 A foreign key in a child table references a primary key in a parent table.
- 🛡️ It prevents actions that would break the link, like deleting a referenced parent row.
- ✅ Maintains consistency by allowing only valid references.
- ↔️ Supports cascading actions (ON DELETE/UPDATE CASCADE) to maintain integrity automatically.
💻 Real Code Example
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
❓ Interview Q&A
Q1: What is a foreign key?
A: It is a constraint that links two tables by referencing a primary key in another table.
Q2: How does a foreign key enforce referential integrity?
A: It ensures that values in the foreign key column correspond to existing rows in the parent table.
Q3: What happens if you try to delete a referenced parent row?
A: The database prevents the deletion unless cascading delete is specified.
Q4: What are cascading actions?
A: Options like ON DELETE CASCADE that propagate changes to child tables automatically.
Q5: Can a foreign key reference a non-primary key column?
A: Usually, it references a primary or unique key column to ensure uniqueness.
📝 MCQs
Q1. What does a foreign key do?
- Creates a copy of data
- Links two tables by referencing a primary key
- Deletes data automatically
- Indexes data
Q2. What is referential integrity?
- Ensuring data encryption
- Ensuring valid references between tables
- Speeding up queries
- Reducing storage
Q3. What happens if you delete a parent row referenced by a foreign key without cascade?
- Deletion is blocked
- Child rows deleted
- Child rows updated
- No effect
Q4. What does ON DELETE CASCADE do?
- Prevents deletion
- Deletes child rows automatically
- Updates parent rows
- Locks tables
Q5. Can a foreign key reference multiple columns?
- No
- Yes, composite foreign keys
- Only single column
- Depends on DBMS
💡 Bonus Insight
Foreign keys are fundamental for relational databases to maintain accurate and consistent relationships, helping avoid orphaned records and ensuring reliable data navigation.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!