0

I have a table where email is a unique key, the point is... when the user get deleted in the manage form, I do an update, inserting "1" in a column called "deleted", doing this I keep the data and the history of that user... But if I have to add a new user with the same email, Bang MySQL catches me

So.. my question is, the best practice is?

  1. Do a remove in table when user get deleted, losing the history of that user
  2. Remove the unique key in the column email, and keep the validate only in JS for prevent duplicates emails
  3. Another one...

Thanks for your time

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Sophie
  • 410
  • 3
  • 10
  • You can change your unique constraint to `email, deleted` instead of just `email` but what if the new user with same email get deleted again? – FanoFN Jan 11 '22 at 03:10
  • Dam... hahah its a looping question .. what do you recomend? – Sophie Jan 11 '22 at 03:12
  • 1
    My suggestion: don't allow emails to be reused. Allow a user to re-open their account with the same email address. Anyone else has to choose a different address (which they would anyway if they choose an email that's already active) – Tangentially Perpendicular Jan 11 '22 at 03:14
  • 1
    create separate table with deleted emails without unique constraint – Iłya Bursov Jan 11 '22 at 03:14

2 Answers2

3

You can restrict emails to being unique only if not deleted with a virtual column:

create table user (
    email varchar(320),
    deleted tinyint,
    undeleted_email varchar(320) as (if(deleted,null,email)) unique
);

fiddle

ysth
  • 96,171
  • 6
  • 121
  • 214
2

You could reverse the logic and instead of storing a nullable deletion mark, store an active mark. Use a check constraint (in MySQL 8+) or a trigger (in lower MySQL versions which don't enforce check constraints) to restrict the possible values of the active mark to one (non-NULL) value (and NULL). Then put the unique constraint on the e-mail address and the active mark combined. That way you can have multiple rows with the same e-mail address and a NULL active mark but maximally one with that address and a non-NULL active mark.

CREATE TABLE user
             (id integer,
              email varchar(256),
              active tinyint,
              PRIMARY KEY (id),
              CHECK (active = 1),
              UNIQUE (email,
                      active));

db<>fiddle

But as a non technical side note, check, if you're legally allowed to keep the history of a user when they delete their account according to the data protection laws applying to you. They may have the right to expect that everything is deleted when they delete their account.

And you could also consider, no to create a new account, if a user comes back, but instead offer them to reactivate their old account.

sticky bit
  • 36,626
  • 12
  • 31
  • 42