Physical vs. Logical Deletion of Database Records

1. Overview

Database management often involves the deletion of records. However, the deletion method can significantly impact database performance, integrity, and usability. There are two primary methods of deletion: physical and logical.

In this article, we’ll explore both the physical and logical methods of deleting database records. We’ll try some practical examples using the Baeldung University database.

Furthermore, we’ll look at the differences between these two methods.

2. Physical Deletion

Physical deletion refers to the actual removal of records from the database. In particular, this method frees up space and can improve performance. Nevertheless, it makes data recovery more difficult if deletion is accidental. Let’s consider an example using the Department table from the Baeldung University database.

Let’s delete a department with id=1 using the DELETE command:

DELETE FROM department WHERE id = 1;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
DELETE 1
Time: 0.003

This command physically removes the record from the database, freeing up space. To confirm the deletion, we can query the Department table:

SELECT * FROM department;
+----+--------------------------------+------+
| id | name                           | code |
|----+--------------------------------+------|
| 2  | Electronics and Communications | EC   |
| 3  | Mechanical Engineering         | ME   |
| 4  | Civil Engineering              | CE   |
| 5  | Mathematics                    | MA   |
+----+--------------------------------+------+
SELECT 4
Time: 0.007s

As we can see, the student with id=1 is no longer present in the table.

3. Logical Deletion

Logical deletion involves marking records as deleted without actually removing them from the database. This method retains the deletion of historical data. Additionally, we achieve logical deletion through two main approaches: adding an is_deleted column or adding a deleted_at column.

3.1. Using an is_deleted Column

In this approach, we add an is_deleted column to the table. In particular, the column indicates whether a record is deleted (true) or not (false).

For example, let’s update the Student table to include an is_deleted column. First, we add the column:

ALTER TABLE Student ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
ALTER TABLE
Time: 0.045s

Next, we logically delete a student by setting the is_deleted column to TRUE:

UPDATE Student SET is_deleted = TRUE WHERE id = 1010;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
UPDATE 1
Time: 0.006s

To query active (non-deleted) students, we simply filter out the records where is_deleted is true:

SELECT * FROM Student WHERE is_deleted IS FALSE;
+------+----------------+-------------+------------+-----------------+-----------------+--------+------------+
| id   | name           | national_id | birth_date | enrollment_date | graduation_date | gpa    | is_deleted |
|------+----------------+-------------+------------+-----------------+-----------------+--------+------------|
| 1001 | John Liu       | 123345566   | 2001-04-05 | 2020-01-15      | 2024-06-15      | 4.0    | False      |
| 1003 | Rita Ora       | 132345166   | 2001-01-14 | 2020-01-15      | 2024-06-15      | 4.2    | False      |
| 1007 | Philip Lose    | 321345566   | 2001-06-15 | 2020-01-15      | 2024-06-15      | 3.8    | False      |
| 1011 | Vikas Jain     | 321345662   | 2001-07-18 | 2020-01-15      | <null>          | 3.3    | False      |
...

This way, the deleted records remain in the database but are excluded from regular queries. As seen in the output, the student with id=1010 is no longer present in the table.

It’s important to note that if we’re working in SQL Server, which doesn’t have a native BOOLEAN type, we can use a different type to implement logical deletion. A common approach is to use a BIT type with 0 representing false and 1 representing true. Alternatively, we could use a VARCHAR type with values such as ‘Y’ or ‘N’.

3.2. Using a deleted_at Column

Another approach involves adding a deleted_at column, which stores the timestamp when a record is deleted. Particularly, it keeps track of when deletions occurred, providing more granular information about the record’s status.

First, we add the deleted_at column if it doesn’t already exist:

ALTER TABLE Student ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
column "deleted_at" of relation "student" already exists, skipping
None

ALTER TABLE
Time: 0.003s

Next, we logically delete a student by setting the deleted_at column to the current timestamp:

UPDATE Student SET deleted_at = CURRENT_TIMESTAMP WHERE id = 1011;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
UPDATE 1
Time: 0.005s

Now, let’s verify the deletion and timestamp of the deleted id of the Student table:

SELECT * FROM Student WHERE id = 1011;
+------+------------+-------------+------------+-----------------+-----------------+------------+----------------------------+
| id   | name       | national_id | birth_date | enrollment_date | graduation_date | is_deleted | deleted_at                 |
|------+------------+-------------+------------+-----------------+-----------------+------------+----------------------------|
| 1011 | Vikas Jain | 321345662   | 2001-07-18 | 2020-01-15      | <null>          | False      | 2024-07-31 08:30:51.857126 |
+------+------------+-------------+------------+-----------------+-----------------+------------+----------------------------+
SELECT 1
Time: 0.008s

As seen in the output, the deleted_at column shows the timestamp when the id was deleted.

Additionally, let’s filter out the records that have been deleted and return only those that have not been deleted:

SELECT * FROM Student WHERE deleted_at IS NULL;
+------+-----------------+-------------+------------+-----------------+-----------------+--------+------------+------------+
| id   | name            | national_id | birth_date | enrollment_date | graduation_date | gpa    | is_deleted | deleted_at |
|------+-----------------+-------------+------------+-----------------+-----------------+--------+------------+------------|
| 1001 | John Liu        | 123345566   | 2001-04-05 | 2020-01-15      | 2024-06-15      | 4.0    | False      | <null>     |
| 1003 | Rita Ora        | 132345166   | 2001-01-14 | 2020-01-15      | 2024-06-15      | 4.2    | False      | <null>     |
| 1007 | Philip Lose     | 321345566   | 2001-06-15 | 2020-01-15      | 2024-06-15      | 3.8    | False      | <null>     |
...

This query returns only the records that have not been logically deleted.

4. Challenges of Cascading Deletion

When dealing with foreign key constraints, deleting a record in a parent table can cause integrity issues in child tables. Let’s explore the challenges of cascading deletions and the methods to handle them.

For instance, suppose we want to delete a student from the table who has graduated, and we use the DELETE command:

DELETE FROM Student WHERE id = 1007;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
update or delete on table "student" violates foreign key constraint "registration_student_id_fkey" on table "registration"
DETAIL:  Key (id)=(1007) is still referenced from table "registration".
Time: 0.008s

We encountered an error after executing the deletion. This error occurs because the Student table has a foreign key constraint with the Registration table. Specifically, the student with id=1007 is still referenced in the Registration table.

To maintain referential integrity, the database prevents the deletion of this student record until the references are handled appropriately.

To solve this, we explore two options: cascading deletions, or manually deleting the related records.

4.1. Cascading Deletion

This approach involves modifying the foreign key constraint to enable cascading deletions. In particular, this means when a record in the Student table is deleted, all related records in the Registration table will also be automatically deleted.

Let’s alter the foreign key constraint to enable cascading deletions:

ALTER TABLE Registration
DROP CONSTRAINT registration_student_id_fkey;
 
ALTER TABLE Registration
ADD CONSTRAINT registration_student_id_fkey
FOREIGN KEY (student_id)
REFERENCES Student (id)
ON DELETE CASCADE;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
ALTER TABLE
ALTER TABLE
Time: 0.033s

After enabling cascading deletions, we can delete the student record:

DELETE FROM Student WHERE id = 1007;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
update or delete on table "student" violates foreign key constraint "exam_student_id_fkey" on table "exam"
DETAIL:  Key (id)=(1007) is still referenced from table "exam".                                                                                                                                                                             
Time: 0.005s

However, another error occurred as a result of additional foreign key constraints from the Exam table.

4.2. Handling Additional Constraints

Again, we enable cascading deletions on the Exam table:

ALTER TABLE Exam
DROP CONSTRAINT exam_student_id_fkey;

ALTER TABLE Exam
ADD CONSTRAINT exam_student_id_fkey
FOREIGN KEY (student_id)
REFERENCES Student (id)
ON DELETE CASCADE;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
ALTER TABLE
ALTER TABLE
Time: 0.013s

After modifying the constraints, we can proceed with the deletion:

DELETE FROM Student WHERE id = 1007;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
DELETE 1
Time: 0.003s

To confirm the deletion, we can query the Student table again:

SELECT * FROM student limit 10;
+------+-----------------+-------------+------------+-----------------+-----------------+
| id   | name            | national_id | birth_date | enrollment_date | graduation_date |
|------+-----------------+-------------+------------+-----------------+-----------------|
| 1001 | John Liu        | 123345566   | 2001-04-05 | 2020-01-15      | 2024-06-15      |
| 1003 | Rita Ora        | 132345166   | 2001-01-14 | 2020-01-15      | 2024-06-15      |
| 1010 | Samantha Prabhu | 3217165566  | 2001-03-21 | 2020-01-15      | 2024-06-15      |
...

As we can see, the student with id=1007 is no longer present in the table.

4.3. Manual Deletion

Alternatively, we can manually delete the records in all referencing tables before deleting the student record:

DELETE FROM Exam WHERE student_id = 1007;
DELETE FROM Registration WHERE student_id = 1007;

DELETE FROM Student WHERE id = 1007;

This approach gives us more control over the deletion process and can be useful if we need to perform additional checks or operations before deletion.

5. Conclusion

In this article, we’ve examined physical and logical deletion methods in database management.

We tried practical examples using the Baeldung University database. By understanding both methods, we can choose the best approach for our needs to ensure data integrity and performance.