`I want to let users delete their accounts but I really don’t want to delete the data from the database. How do I do that?`
While this may not be ethical, it depends on your terms of service and the laws governing your location/app. However, an ideal use case would be to have sufficient data for analytics. To answer the question, a simple way to accomplish that is to use soft delete
soft deletion (plural soft deletions) (databases) An operation in which a flag is used to mark data as unusable, without erasing the data itself from the database. Wiktionary
What this simply means is that you will add a column to your database, let’s call it is_deleted. It will have a boolean datatype and it’s value will be false by default because the new record has not been deleted yet.
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(20) NOT NULL UNIQUE,
password TEXT NOT NULL,
is_deleted BOOLEAN DEFAULT false
);
When the user tries to delete that record, you only need to update the is_deleted column and set its value to true.
UPDATE users SET is_deleted = true WHERE id = 2;
The next step is to ensure you include the constraint to exclude soft deleted records from your queries.
SELECT username FROM users WHERE is_deleted = false;
You may want to take it a step further and record when the record was deleted. For that, I recommend the approach used in Laravel’s documentation. Instead of adding another column to store the deletion timestamp, simply add a column called deleted_at, set the datatype as datetime and make it null by default.
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(20) NOT NULL UNIQUE,
password TEXT NOT NULL,
deleted_at DATETIME DEFAULT null
);
This time, when the user tries to delete that record, you need to update the record and set the value of deleted_at to the current timestamp.
UPDATE users SET deleted_at = NOW() WHERE id = 2;
Then you’ll query your records like so:
SELECT username FROM users WHERE deleted_at IS NOT NULL;
That’s it!
`How do I implement soft delete on records with the UNIQUE constraint?’
Look forward to my next post, I’ll do my best to answer then.