In the previous lesson, we tested:

🔥 ON DELETE CASCADE

When we deleted a user, all their related photos were automatically deleted.

Now we’re going to explore a very different behavior:

🟡 ON DELETE SET NULL

Instead of deleting related records…
we simply remove the relationship.

---

🏗 Step 1: Drop the Photos Table Again

To test a new delete behavior, we reset our table:

DROP TABLE photos;

This completely removes the table and its data.

---

🧱 Step 2: Recreate Photos Table with ON DELETE SET NULL

Now we recreate the table — but this time we add:

ON DELETE SET NULL

Here’s the full SQL:

CREATE TABLE photos (
    id SERIAL PRIMARY KEY,
    url VARCHAR(200),
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);

⚠ Important:

If we try to insert photos tied to a user that no longer exists (like user ID 1, which we deleted earlier), we will get a foreign key error.

So for this test, we insert photos tied to an existing user (for example, user ID 4).

---

📦 Sample Data

Let’s say our database looks like this:

Users

Photos

Notice:

👉 User 4 has two photos.

---

💣 Step 3: Delete the User

Now we delete user 4:

DELETE FROM users
WHERE id = 4;

The deletion succeeds.

But what happens to the photos?

---

🔍 Step 4: Check the Photos Table

SELECT * FROM photos;

Result:

The photos were NOT deleted.

Instead:

👉 Their user_id column was automatically set to NULL.

---

🧠 What Just Happened?

Because we defined:

ON DELETE SET NULL

PostgreSQL did this:

1. Delete user 4

2. Find all photos referencing user 4

3. Set their user_id value to NULL

The relationship is removed —
but the photo record remains.

---

🆚 CASCADE vs SET NULL

---

🎯 When Would We Use SET NULL?

This is useful when:

🚫 Banning a User

You delete a problematic user account
But you want to keep their posts/photos visible.

📦 Preserving Historical Data

You remove the parent record
But child data still has value.

📰 Content Platforms

A user gets deleted
But their published content remains public.

---

⚠ Important Requirement

For SET NULL to work:

The foreign key column must allow NULL values.

If you define:

user_id INTEGER NOT NULL

Then ON DELETE SET NULL will fail.

---

💡 Mental Model

Think of it like this:

• CASCADE → Destroy everything connected

• SET NULL → Disconnect but keep the data

---

🏁 What’s Next?

We’ve now tested:

✅ CASCADE
✅ SET NULL

Next up:

🟢 ON DELETE SET DEFAULT

Where instead of deleting or nulling —
we assign a predefined default value.