I have 2 tables in a database, users
and users_removed
with columns "id(primary key), email(unique), password" and "id, user_id(foreign key (user_id) references users(id)" respectively.
When a user registers the users
table gets the data accordingly. And when the user wants to delete account I can get user’s id in users_removed
and consider it deleted such as
INSERT into users_removed (user_id)
VALUES ((SELECT id FROM users WHERE email = '[email protected]'))
The id
from users
gets inserted into users_removed
with a foreign key constraint.
Now the question is what will be the right way to get rid of data from users
with that id
but preserve it somehow.
- Deleting entirely is not an option because I loose data and so the purpose of the table
users_removed
. Also if I delete I get error "Cannot delete or update a parent row: a foreign key constraint fails" because of the foreign key constraint. - The user should be able to re-register with previous email but considering it an entirely new entry, as
email
inusers
is unique.
Is there a way in sql to make certain data unable to be used, disallow to perform query on it, such as it gets ignored when I perform query in the backend.
Or what could be the possible ways to the solution?
I have a way of restricting users_removed
to be able to login, but how should I proceed with the registration thing.
2
Answers
As mysql doesn’t allow rerecly to use a select in the INSERT and delete from the same table, you must corcumvent ideally in a programming language out side of mysql.
I used here a seperate SELCT with a user defined variable, to get first the user_id
fiddle
IMHO it will better to add two fields (IsDeleted, DeletedAt) to the users table.
You will include (IsDeleted=0) within your conditions in every query that deals with users.
when the user wants to delete account, You will set IsDeleted to 1 and DeletedAt to NOW().
To make user able to re-register with previous email you will check for a unique index on fields (email, IsDeleted) not on (email).
If query returns (0) then the user can use that email.
you may remove the data of users with IsDeleted=1 after a specified period of time has elapsed from the date of deletion (DeletedAt).
Example :
If you want to remove user and its data after one year, to get users you will remove :
then you will delete data from related tables for these users.