By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE user
(id integer,
email varchar(256),
active tinyint,
PRIMARY KEY (id),
CHECK (active = 1),
UNIQUE (email,
active));
INSERT INTO user
(id,
email,
active)
VALUES (1,
'somebody@somewhere',
1);
INSERT INTO user
(id,
email,
active)
VALUES (2,
'somebody@somewhere',
1);
Duplicate entry 'somebody@somewhere-1' for key 'user.email'
UPDATE user
SET active = NULL
WHERE id = 1;
INSERT INTO user
(id,
email,
active)
VALUES (2,
'somebody@somewhere',
1);
INSERT INTO user
(id,
email,
active)
VALUES (3,
'somebody@somewhere',
1);
Duplicate entry 'somebody@somewhere-1' for key 'user.email'
SELECT *
FROM user;
| id | active | |
|---|---|---|
| 1 | somebody@somewhere | null |
| 2 | somebody@somewhere | 1 |