論理削除と一意性制約を両立させる方法・DB製品別 ref: http://qiita.com/yuba/items/70165875cfe02b03513d

file0.sql

CREATE UNIQUE INDEX mail_is_unique ON users (mail)
WHERE status = 0;

file1.sql

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY,
    mail VARCHAR NOT NULL,
    status INT CHECK (status = 0),
    CONSTRAINT mail_is_unique UNIQUE (mail, status)
);