使用SQL唯一约束替代触发器实现数据插入限制需求

这问题我太熟了！要实现你说的「Deleted=0时Date+Passport唯一，Deleted=1时允许重复」的逻辑，不用触发器，直接用部分/过滤唯一索引就能搞定，主流数据库都支持，我给你逐个讲清楚：

核心逻辑拆解 #

咱们要的是「条件性唯一约束」：只有当Deleted=0时，(Date, Passport)的组合才必须唯一；Deleted=1的记录不受这个限制。本质是给特定条件的行加唯一索引。

各数据库实现方案 #

PostgreSQL #

PostgreSQL原生支持部分唯一索引，语法直接明了：

-- 替换成你的表名
CREATE UNIQUE INDEX idx_unique_active_passport_date
ON your_table_name (Date, Passport)
WHERE Deleted = 0;

验证你的操作流程：

• 插入Id=1, Date='2018-05-01', Passport='MPEUIE80', Deleted=0：完全没问题，此时没有同组合且Deleted=0的行。
• 将这条记录标记为删除：执行UPDATE your_table_name SET Deleted=1 WHERE Id=1;，操作成功后，这条记录不再受唯一索引约束，之后你可以插入任意多条同Date+Passport且Deleted=1的记录，也能再插入一条同组合但Deleted=0的新记录。

MySQL #

MySQL 8.0.13+支持表达式索引，低版本可以用生成列间接实现：

方案1：表达式索引（MySQL 8.0.13+推荐）

CREATE UNIQUE INDEX idx_unique_active_passport_date
ON your_table_name (Date, Passport, (CASE WHEN Deleted = 0 THEN 1 ELSE NULL END));

方案2：生成列兼容低版本

-- 先新增一个生成列，Deleted=0时为1，否则为NULL
ALTER TABLE your_table_name
ADD COLUMN active_flag BOOLEAN GENERATED ALWAYS AS (CASE WHEN Deleted = 0 THEN 1 ELSE NULL END) STORED;

-- 给(Date, Passport, active_flag)加唯一索引
CREATE UNIQUE INDEX idx_unique_active_passport_date
ON your_table_name (Date, Passport, active_flag);

原理：MySQL的唯一索引中，NULL值不会被视为重复项。所以Deleted=1时active_flag为NULL，不会触发约束；Deleted=0时active_flag=1，此时(Date, Passport, 1)必须唯一。

SQL Server #

SQL Server支持过滤索引，和PostgreSQL的部分索引逻辑一致：

CREATE UNIQUE NONCLUSTERED INDEX idx_unique_active_passport_date
ON your_table_name (Date, Passport)
WHERE Deleted = 0;

效果和PostgreSQL完全相同：只有Deleted=0的行才会被检查Date+Passport的唯一性。

注意事项 #

• 确保Date字段是日期类型（DATE/DATETIME），避免用字符串存储导致的匹配误差。
• 索引名称可以根据你的业务场景自定义，比如改成idx_{表名}_active_passport_date更清晰。
• 测试边界场景：尝试插入两条Deleted=0且Date+Passport相同的记录，数据库应该抛出唯一性约束错误；插入多条Deleted=1的同组合记录则能成功。

内容的提问来源于stack exchange，提问作者WltrRpo