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

id

username

2

pfeffer

3

99stroman

4

sim3onis

Photos

id

url

user_id

6

http://img6.jpg

3

7

http://img7.jpg