FOREIGN KEY – Connecting Tables Like a Pro

🔗 FOREIGN KEY – Connecting Tables Like a Pro
Because real databases don’t live in isolation.

Quick Syntax to Create a FOREIGN KEY

CREATE TABLE child_table (
   column_name datatype,
   FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)
);

This tells SQL: “This column must match a value in the other table’s PRIMARY KEY.”
It creates a relationship between two tables, and enforces data integrity.

Real-Life Analogy: Student + Class List

Let’s say you have two tables:

1. classes (the parent table)

class_id	class_name
101	Math
102	Science
103	History

2. students (the child table)

student_id	name	class_id
1	John	101
2	Sarah	102
3	Emma	999

Uh-oh! Class 999 doesn’t exist in the classes table!
A FOREIGN KEY would prevent this mistake.

Fix It: Add a FOREIGN KEY

CREATE TABLE classes (
   class_id INT PRIMARY KEY,
   class_name VARCHAR(50)
);

CREATE TABLE students (
   student_id INT PRIMARY KEY,
   name VARCHAR(50),
   class_id INT,
   FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

Output (after fix):

student_id	name	class_id
1	John	101
2	Sarah	102
3	Emma	103

Inserting class_id = 999 now will be rejected by SQL.

Add FOREIGN KEY with ALTER TABLE

ALTER TABLE students
ADD CONSTRAINT fk_class
FOREIGN KEY (class_id)
REFERENCES classes(class_id);

You can give the constraint a name like fk_class to make it easier to manage.

Key Rules About FOREIGN KEYS

Rule / Feature	What It Means
Must match PRIMARY KEY	REFERENCES must point to a PRIMARY or UNIQUE column
Same data type	Column types must match (e.g., INT to INT)
Enforces integrity	Prevents inserting values that don’t exist in the parent
One-to-many relationships	Very common use case (e.g., one class → many students)

Common Mistakes to Avoid

Mismatched data types

-- Won’t work:
class_id INT in parent
class_id VARCHAR in child

Deleting referenced parent data

DELETE FROM classes WHERE class_id = 101;
--  SQL will block this if students still point to class 101

Recap – You Were Here

• Started with disconnected tables and possible bad data
• Created a relationship with FOREIGN KEY
• Now your tables are connected, safe, and reliable

Bottom line: A FOREIGN KEY is like saying, “You can only use values that actually exist in that other table.”