The DELETE command in SQL allows us to remove one or more records from a table. However, it's important to use DELETE with caution, as once records are deleted, they cannot be recovered unless backups have been made beforehand. In this chapter, we will learn how to use DELETE efficiently and safely.

Deleting Records with DELETE

The basic use of DELETE is to remove records that meet a certain condition. Below is a basic example of how to delete a user in the users table whose id is 1:

sql

Cautious Use of DELETE

It is crucial that we always use the WHERE clause with DELETE to avoid accidentally deleting all records from a table. If WHERE is omitted, all records will be deleted.

sql

Deleting Multiple Records

We can delete multiple records in a single operation if the WHERE clause selects more than one record. Below is an example to delete all users whose email ends with 'example.com':

sql

Cascade Delete

When working with related tables, it is common for records in one table to depend on records from another. If we delete a record that has dependencies, we might want to automatically delete the related records. This is achieved with cascade delete, which is defined when creating relationships between tables.

For example, if we have defined a relationship between users and orders, we could set up cascade delete like this:

sql

Soft Delete: Logical Deletion

Instead of permanently deleting records from a table, many applications use a technique known as soft delete or logical deletion. With this technique, instead of deleting the record, we simply mark the record as deleted using an additional field, such as deleted_at or is_deleted.

Soft Delete Example

sql

The advantage of this technique is that we can "restore" the record if needed, simply by updating the value of the is_deleted field.

Summary

In this chapter, we learned how to use the DELETE command to remove records from a SQL database. We saw how to use the WHERE clause to avoid deleting all records, how to implement cascade delete, and the alternative of soft delete to preserve data. In the next chapter, we will explore how to handle transactions in SQL to ensure data integrity during complex operations.