SQL FOREIGN KEY Constraint

What Is a FOREIGN KEY in SQL?

A FOREIGN KEY is a constraint used to establish a relationship between two tables. It ensures that the value in one table must exist in another, keeping your data consistent and connected.

Key Concepts

References a column in another table (usually a PRIMARY KEY)
Enforces referential integrity - prevents invalid references
Used to model real-world relationships (e.g., users and their videos)
Enables efficient joining of related data

Creating Tables with FOREIGN KEYs

Let's look at our video sharing platform schema with foreign key relationships:

1. Users Table (Referenced Table)

2. Videos Table (References users)

3. Interactions Table (References both)

Adding FOREIGN KEYs to Existing Tables

You can add foreign key constraints to existing tables using ALTER TABLE:

ON DELETE and ON UPDATE Actions

You can specify what happens when referenced records are updated or deleted:

Available Actions:

CASCADE: Automatically delete/update child records
RESTRICT: Prevent deletion/update if child records exist
SET NULL: Set the foreign key to NULL
NO ACTION: Similar to RESTRICT (default behavior)

Best Practices

Tips for Using FOREIGN KEYs

Always index foreign key columns for better JOIN performance
Choose appropriate ON DELETE/UPDATE actions based on business rules
Use NOT NULL for foreign keys unless nullable relationships make sense
Reference only PRIMARY KEY or UNIQUE columns
Keep relationships simple and meaningful

Common Interview Questions

Q: What is referential integrity and how do foreign keys enforce it?

Referential integrity ensures that relationships between tables remain consistent. Foreign keys enforce this by:

Preventing insertion of records with invalid references
Managing cascading updates/deletes of related records
Ensuring data consistency across tables

Q: In our schema, what happens if we try to delete a user who has videos?

By default (NO ACTION), the delete would be prevented to maintain referential integrity. To allow deletion, you would need to either:

First delete all the user's videos and interactions
Use ON DELETE CASCADE in the foreign key definition
Use ON DELETE SET NULL (if the foreign key allows NULL values)

Interview Master

SQL Learning Hub