Interview MasterSQL Learning
SQL FOREIGN KEY Constraint
SQL Learning
SQL FOREIGN KEY Constraint
Learn how to use FOREIGN KEY to create relationships between tables and enforce referential integrity.
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
- It references a column in another table (usually a
PRIMARY KEY) - It enforces referential integrity
- Used to model real-world relationships (e.g. users and their posts)
Basic FOREIGN KEY Syntax
CREATE TABLE Interactions (
interaction_id INTEGER PRIMARY KEY,
user_id INTEGER,
video_id INTEGER,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (video_id) REFERENCES Videos(video_id)
);This ensures every user_id in the Interactions table must already exist in the Users table.
Defining FOREIGN KEYS with ALTER TABLE
You can also add foreign keys after the table has been created.
ALTER TABLE Interactions
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES Users(user_id);Real-World Example
Let's define Users and Videos and connect them through Interactions:
CREATE TABLE Users (
user_id INTEGER PRIMARY KEY,
username TEXT NOT NULL
);
CREATE TABLE Videos (
video_id INTEGER PRIMARY KEY,
title TEXT
);
CREATE TABLE Interactions (
interaction_id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
video_id INTEGER NOT NULL,
interaction_type TEXT,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (video_id) REFERENCES Videos(video_id)
);Best Practices for FOREIGN KEYS
- Always index your foreign key columns for faster JOINs
- Use
ON DELETEandON UPDATErules to manage cascading changes - Don't reference non-unique or nullable columns
- Keep foreign key relationships simple and meaningful