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.

1. 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.
2. The user should be able to re-register with previous email but considering it an entirely new entry, as email in users 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.

CodePudding user response：

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

CREATE TABLE users (id int AUTO_INCREMENT PRIMARY key, email varchar(100) UNIQUE)

CREATE TABLE  users_removed (id int AUTO_INCREMENT PRIMARY key,user_id int) 

INSERT INTO users (email) VALUES ('[email protected]')

CREATE TRIGGER after_users_removed_insert
AFTER INSERT
ON users_removed FOR EACH ROW
BEGIN
    IF NEW.user_id IS NOT NULL THEN
DELETE FROM users WHERE id = new.user_id;
    END IF;
END

SELECT id INTO @user_id FROM users WHERE email = '[email protected]'  ;
INSERT into users_removed (user_id) 
VALUES (@user_id)

SELECT * FROM users

INSERT INTO users (email) VALUES ('[email protected]')

SELECT * FROM users

fiddle

CodePudding user response：

IMHO it will better to add two fields (IsDeleted, DeletedAt) to the users table.

CREATE TABLE usersss (
  id int unsigned NOT NULL auto_increment,
  email varchar(100),
  IsDeleted tinyInt default 0,
  DeletedAt datetime,
  PRIMARY KEY (id),
  UNIQUE KEY uk_email (email, IsDeleted),
  KEY IsDeleted (IsDeleted)
);

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().

UPDATE users SET IsDeleted=1 AND DeletedAt = NOW() WHERE id=$ID;

To make user able to re-register with previous email you will check for a unique index on fields (email, IsDeleted) not on (email).

SELECT COUNT(id) FROM users WHERE (IsDeleted=0) AND (email = '$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 :

SELECT id FROM users WHERE TO_DAYS(NOW()) >= (TO_DAYS(DeletedAt)   365) ;

then you will delete data from related tables for these users.