Understanding CASCADE DELETE | Noroff Front-end Development

What is CASCADE DELETE?

When we created the posts table, we included ON DELETE CASCADE in the foreign key constraint. This means that when a user is deleted, all their posts are automatically deleted too.

This prevents orphaned data - posts that reference a user who no longer exists.

Demonstrating CASCADE DELETE

Let’s see CASCADE DELETE in action. First, pick a user ID from your users table and check their posts:

SELECT * FROM posts WHERE user_id = 4;

Now delete that user:

DELETE FROM users WHERE id = 4;

Check that user’s posts again:

SELECT * FROM posts WHERE user_id = 4;

You’ll notice that:

The user’s posts have been automatically deleted.
No posts exist with that user_id anymore.

Why CASCADE DELETE Matters

Data Integrity Protection

Automatic cleanup: When you delete a parent record, all related child records are automatically removed.
No broken relationships: Prevents child records from pointing to parent records that don’t exist.
Consistent data: Ensures your database never contains invalid references that could confuse your application.
Saves manual work: You don’t have to remember to delete related records in the correct order.

Alternative Approaches

Without CASCADE DELETE, you would need to manually delete related data:

DELETE FROM posts WHERE user_id = 4;
DELETE FROM users WHERE id = 4;

This manual approach requires you to remember to delete posts first, then the user. If you forget this order, the database will reject the user deletion because posts still reference that user.

When to Use CASCADE DELETE

Use when child data doesn’t make sense without parent (posts without authors).
Don’t use when child data should persist (orders should remain even if customer account is closed).

Using CASCADE DELETE in Your API

Test the CASCADE DELETE behaviour through your API by creating a DELETE endpoint:

app.delete("/users/:id", async (req, res) => {
  try {
    const userId = Number(req.params.id);

    if (isNaN(userId)) {
      return res.status(400).json({ error: "Invalid user ID" });
    }

    const [result]: [ResultSetHeader, any] = await pool.execute(
      "DELETE FROM users WHERE id = ?",
      [userId]
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "User not found" });
    }

    res.status(204).send();
  } catch (error) {
    console.error("Error deleting user:", error);
    res.status(500).json({ error: "Failed to delete user" });
  }
});

This endpoint demonstrates CASCADE DELETE in action. When you delete a user, the database automatically deletes all their posts due to the ON DELETE CASCADE constraint. The endpoint returns 204 No Content (consistent with REST standards from Lesson 3), and you can verify the CASCADE DELETE worked by checking the posts table directly.

Tags: