MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version()

8.0.36

create table User
(
  id int NOT NULL,
  Username VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX unique_user
ON User(Username, (CASE WHEN  Username = 'Deleted' THEN id ELSE 0 END));

Records: 0  Duplicates: 0  Warnings: 0

insert into User (id, Username) values (1, 'John'), (2, 'Deleted');

Records: 2  Duplicates: 0  Warnings: 0

insert into User (id, Username) values (3, 'John');

Duplicate entry 'John-0' for key 'User.unique_user'

insert into User (id, Username) values (4, 'Deleted');
select * from User;

id
Username

2
Deleted

4
Deleted

1
John