🔗 Foreign Keys = Data Relationships
Data needs relationships. Foreign keys link tables. Referential integrity, joins, constraints.
📝 Foreign Key Basics
-- Create with foreign key
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
total DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- With ON DELETE CASCADE
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);
-- With ON UPDATE CASCADE
CREATE TABLE products (
id INT PRIMARY KEY,
category_id INT,
FOREIGN KEY (category_id) REFERENCES categories(id) ON UPDATE CASCADE
);
-- Add foreign key after creation
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(id);
# Referential Actions
- NO ACTION: Prevent delete/update
- CASCADE: Cascade changes
- SET NULL: Set foreign key to NULL
- SET DEFAULT: Set to default value
🎯 Foreign Key Examples
# ON DELETE CASCADE
CREATE TABLE comments (
id INT PRIMARY KEY,
post_id INT,
content TEXT,
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);
-- Deleting a post deletes all its comments
# ON DELETE SET NULL
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);
-- Deleting a user sets user_id to NULL
# Check foreign key constraints
SHOW CREATE TABLE orders;
# Drop foreign key
ALTER TABLE orders DROP FOREIGN KEY fk_orders_users;
# Disable foreign key checks (temporary)
SET FOREIGN_KEY_CHECKS = 0;
-- Operations here
SET FOREIGN_KEY_CHECKS = 1;
# Foreign Key Benefits
- Data integrity (referential)
- Prevents orphaned records
- Cascading operations
- Relationship enforcement
- Self-documenting schema
💡 Foreign Key Tips
- Use for data integrity
- Prevents orphaned records
- Use cascading carefully
- Index foreign key columns
- Self-documenting schema
“Foreign keys link tables. Referential integrity, joins, constraints. Essential for relational databases.”

