🔐 Constraints Around Deletion (Foreign Keys in Action) | HiringMine
Community · 6 min read 🔐 Constraints Around Deletion (Foreign Keys in Action) So far, we’ve learned: - What primary keys are - What foreign keys are - How insertion behaves when foreign keys are involved Now it’s time to look at the opposite oper
MS Muhammad Sufiyan Software Engineer · 5d ago
Backend Engineering Hub T-
So far, we’ve learned:
What primary keys are What foreign keys are How insertion behaves when foreign keys are involved Now it’s time to look at the opposite operation :
❓ What happens when we try to DELETE a record that other records depend on? This is where foreign key constraints really start to shine.
🧠 The Scenario We have two tables:
🧍 Users Table id
username
1
monahan93
2
pfeffer
3
99stroman
4
sim3onis
📸 Photos Table id
url
user_id
2
http://img2
1
3
http://img3
1
4
http://img4
1
Notice something important:
👉 User with ID 1 has three photos associated with them.
❓ What If We Delete User 1? If we run:
DELETE FROM users
WHERE id = 1;
What would happen?
If this deletion were allowed, then:
Those three photos would still exist But they would reference user_id = 1 And that user would no longer exist This creates something called:
⚠️ Dangling References A dangling reference means:
A foreign key is pointing to a row that no longer exists. That’s bad database design.
Because we used SERIAL for IDs, PostgreSQL will never reuse ID 1 again .
So those photos would permanently reference a user that will never exist again.
By default, PostgreSQL protects you.
DELETE FROM users
WHERE id = 1;
You’ll get an error like:
❌ violates foreign key constraint Because the default behavior is:
"You cannot delete a parent record if child records still reference it." This prevents data corruption.
⚙️ Different Delete Options When defining a foreign key, we can control what happens on deletion.
Here are the main options:
Throws an error if child records exist.
✔ Prevents accidental data loss
2️⃣ ON DELETE NO ACTION Almost identical to RESTRICT.
It also prevents deletion, but the timing of the check is slightly different internally.
👉 Treat it the same as RESTRICT.
3️⃣ ON DELETE CASCADE DELETE FROM users WHERE id = 1;
Delete the user Automatically delete all photos referencing that user 💡 This is commonly used when:
Deleting a user account Deleting an order and its items Removing a blog post and its comments
4️⃣ ON DELETE SET NULL Instead of deleting the photos…
So photos remain, but no longer belong to anyone.
You want to keep data But break the relationship
5️⃣ ON DELETE SET DEFAULT If the column has a default value defined:
user_id INTEGER DEFAULT 999
Then deleting the user would set:
This is less common but useful in specific systems.
🧪 Testing Deletion Constraints in PostgreSQL In the previous lesson, we learned about different ON DELETE constraints :
RESTRICTNO ACTIONCASCADESET NULLSET DEFAULTNow it’s time to stop theorizing…
🔥 Let’s test them for real. Drop the photos table Recreate it Apply different ON DELETE rules Observe what happens when we delete a user This is where things become crystal clear.
🗑 Step 1: Drop the Photos Table Before testing anything, we reset the table:
👉 It completely deletes the table and all data inside it.
❌ relation "photos" does not exist
🏗 Step 2: Recreate the Photos Table (With ON DELETE CASCADE) Now we recreate the table — but this time we add:
CREATE TABLE photos (
id SERIAL PRIMARY KEY,
url VARCHAR(200),
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);
Then insert some sample data:
INSERT INTO photos (url, user_id) VALUES
('http://img2.jpg', 1),
('http://img3.jpg', 1),
('http://img4.jpg', 1),
('http://img5.jpg', 2),
('http://img6.jpg', 3),
('http://img7.jpg', 4);
Now our database looks like this:
Users
Photos 👉 User 1 has three photos.
💣 Step 3: Test ON DELETE CASCADE DELETE FROM users
WHERE id = 1;
Result: Photos with user_id = 1 are automatically gone.
The photos that belonged to user 1 have been deleted automatically.
🔥 What Just Happened? PostgreSQL did this automatically:
1. Delete user 1 2. Find all photos referencing user 1 3. Delete those photos too
No manual cleanup required.
No dangling foreign keys.
🎯 When Do We Use CASCADE? Very common real-world cases:
📝 Blog System Blog Post → Comments Delete post → delete comments
💬 Forum Thread → Replies Delete thread → delete replies
👤 User Account User → Posts / Photos / Likes Delete user → delete all their data
🧠 Why This Is Powerful Without CASCADE, you would need:
Manual deletion logic Extra cleanup queries Risk of forgetting dependent data With CASCADE, the database handles it safely.
⚠ Important Warning ON DELETE CASCADE is powerful — but dangerous if misused.
If applied incorrectly, deleting one record could wipe out huge amounts of related data.
Should deleting the parent really delete all children? HIRINGMINE CAREER SIGNAL This writing is proof of expertise. Explore the author’s verified skills, projects and availability—or start a professional conversation.
MS WRITTEN BY Muhammad Sufiyan Software Engineer. Writing about practical work and career growth.
View profile 
🛑 Default Behavior: ON DELETE RESTRICT
1️⃣ ON DELETE RESTRICT (Default)